Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

From Query Telemetry to a Verified Faster Plan: Applying an Index Recommendation End to End

You will take a slow query surfaced by production telemetry, apply the index it recommends, and prove the plan actually improved by running EXPLAIN (ANALYZE, BUFFERS) before and after the CREATE INDEX on a table seeded with 500,000 rows. Every plan shown below is real psql output captured from PostgreSQL 16.15, including the cleanup, so you can reproduce the exact same transition from a parallel Seq Scan to an Index Scan on your own database.

What You'll Build

A short, repeatable workflow with four parts:

  1. A self-created demo table (search_demo_<timestamp>) with 500,000 rows and a high-cardinality customer_email column, so a missing index materially hurts.
  2. A BEFORE EXPLAIN (ANALYZE, BUFFERS) of an equality lookup, saved for comparison.
  3. The recommended index, created with CREATE INDEX, followed by an AFTER run of the identical EXPLAIN statement.
  4. A side-by-side read of both plans, plus cleanup with DROP INDEX and DROP TABLE.

Once you have a recommendation from your production query telemetry, the loop is the same: read the suggested column list, translate it into a CREATE INDEX statement, and re-run the plan check to confirm the planner agrees. Because Neki runs standard Postgres on every shard, the same psql workflow applies there without special tooling.

Prerequisites

  • A PostgreSQL 14+ database you can run DDL against. Neki exposes standard Postgres, so any psql client works.
  • psql installed locally.
  • If you are connecting to a Neki database, your environment should carry the sandbox-provided variables PLANETSCALE_ORG, PLANETSCALE_DATABASE, PLANETSCALE_BRANCH, PLANETSCALE_SERVICE_TOKEN, and PLANETSCALE_SERVICE_TOKEN_ID. Derive a connection string at runtime with:
pscale role create --format json

Use the returned credentials to build your postgres://... connection string and export it as DATABASE_URL for the psql calls below.

Implementation

Step 1: Create and seed a table where the index matters

Use a run-unique suffix so you never collide with an existing table. Half a million rows with unique email values makes a Seq Scan expensive and an equality lookup highly selective.

CREATE TABLE search_demo_1793863181 (
  id bigint generated always as identity primary key,
  customer_email text not null,
  payload text
);

INSERT INTO search_demo_1793863181 (customer_email, payload)
SELECT 'user' || g || '@example.com', md5(g::text) || '-payload'
FROM generate_series(1, 500000) g;

ANALYZE search_demo_1793863181;

The final ANALYZE refreshes planner statistics so the BEFORE plan reflects real data distribution.

Step 2: Capture the BEFORE plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM search_demo_1793863181
WHERE customer_email = 'user424242@example.com';

Observed output:

                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..9777.27 rows=1 width=49) (actual time=36.132..39.622 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=6173
   ->  Parallel Seq Scan on search_demo_1793863181  (cost=0.00..8777.17 rows=1 width=49) (actual time=30.109..31.915 rows=0 loops=3)
         Filter: (customer_email = 'user424242@example.com'::text)
         Rows Removed by Filter: 166666
         Buffers: shared hit=6173
 Planning:
   Buffers: shared hit=96 read=1
 Planning Time: 0.285 ms
 Execution Time: 39.652 ms
(12 rows)

Save this output. The numbers to compare later: Parallel Seq Scan, Buffers: shared hit=6173, and Execution Time: 39.652 ms.

In production, the recommended column list comes from your query telemetry: the slow query's predicates tell you which columns the index should cover. Here the recommendation is a btree on customer_email, because the lookup filters on equality against that column:

CREATE INDEX idx_search_demo_email_1793863181
  ON search_demo_1793863181 (customer_email);

Step 4: Re-run the identical EXPLAIN

Run the exact same statement from Step 2, unchanged:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM search_demo_1793863181
WHERE customer_email = 'user424242@example.com';

Observed output:

                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_search_demo_email_1793863181 on search_demo_1793863181  (cost=0.42..8.44 rows=1 width=49) (actual time=0.030..0.031 rows=1 loops=1)
   Index Cond: (customer_email = 'user424242@example.com'::text)
   Buffers: shared hit=1 read=3
 Planning:
   Buffers: shared hit=85 read=1
 Planning Time: 0.230 ms
 Execution Time: 0.050 ms
(7 rows)

Complete Example

The full session, in order, as one script:

-- 1. Seed
CREATE TABLE search_demo_1793863181 (
  id bigint generated always as identity primary key,
  customer_email text not null,
  payload text
);
INSERT INTO search_demo_1793863181 (customer_email, payload)
SELECT 'user' || g || '@example.com', md5(g::text) || '-payload'
FROM generate_series(1, 500000) g;
ANALYZE search_demo_1793863181;

-- 2. BEFORE plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM search_demo_1793863181
WHERE customer_email = 'user424242@example.com';

-- 3. Apply the recommendation
CREATE INDEX idx_search_demo_email_1793863181
  ON search_demo_1793863181 (customer_email);

-- 4. AFTER plan (identical statement)
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload FROM search_demo_1793863181
WHERE customer_email = 'user424242@example.com';

-- 5. Cleanup
DROP INDEX idx_search_demo_email_1793863181;
DROP TABLE search_demo_1793863181;

How It Works

The plan transition. BEFORE, the planner ran a Parallel Seq Scan: three workers each filtered about 166,666 rows to find one match, touching 6,173 buffers. AFTER, it used an Index Scan with an Index Cond, touching 4 buffers total (shared hit=1 read=3). Execution time dropped from 39.652 ms to 0.050 ms, roughly a 790x improvement on this lookup.

Row estimates matched. Both plans estimated rows=1 and actually returned rows=1. When estimates and actuals diverge badly, the planner can ignore a good index, so check this line in both plans.

If the planner refuses the new index. Sometimes the AFTER plan still shows a Seq Scan, usually because statistics are stale or selectivity is too low. Run ANALYZE search_demo_1793863181; after the CREATE INDEX and re-run the EXPLAIN. If the plan still does not change, do not talk yourself into seeing an improvement that is not there: record the actual result, for example BLOCKED: planner chose not to use index, and investigate selectivity, column correlation, or whether the query shape matches the recommendation. A fabricated AFTER plan is worse than a blocked one because it hides a real problem.

Why BUFFERS matters. Timing varies by machine and cache warmth; buffer counts do not. shared hit=6173 versus hit=1 read=3 is hardware-independent proof that the index eliminated a full-table walk.

Cleanup. DROP INDEX and DROP TABLE remove the demo objects so the exercise leaves no residue, which matters if you ran it against a shared database.

Conclusion

Applying an index recommendation is only half the job; the other half is proving it worked. Seed data where the missing index hurts, capture a BEFORE EXPLAIN (ANALYZE, BUFFERS), create the index, re-run the identical statement, and compare scan type, buffers, and execution time. If the planner disagrees with the recommendation, say so and dig into statistics and selectivity rather than assuming success. The same verify-after-apply loop is how you should treat every index suggestion your telemetry surfaces.

verification:
  status: verified
  tested_at: "2026-11-05"
  product_version: "PostgreSQL 16.15 (psql against a Neki-compatible Postgres endpoint)"
  command: "create a uniquely named demo table with 500,000 rows of unique customer_email values, run EXPLAIN (ANALYZE, BUFFERS) on an equality lookup, add a btree index on customer_email, re-run the identical EXPLAIN, then drop the index and table"
  expected_result: "BEFORE plan shows a Parallel Seq Scan with Buffers: shared hit=6173 and Execution Time ~39.652 ms; AFTER plan shows an Index Scan with Index Cond, Buffers: shared hit=1 read=3, and Execution Time ~0.050 ms; cleanup drops the index and table without errors"