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:
- A self-created demo table (
search_demo_<timestamp>) with 500,000 rows and a high-cardinalitycustomer_emailcolumn, so a missing index materially hurts. - A BEFORE
EXPLAIN (ANALYZE, BUFFERS)of an equality lookup, saved for comparison. - The recommended index, created with
CREATE INDEX, followed by an AFTER run of the identicalEXPLAINstatement. - A side-by-side read of both plans, plus cleanup with
DROP INDEXandDROP 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, andPLANETSCALE_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.
Step 3: Apply the recommended index
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"