9 PostgreSQL fixes that took real queries from ~100 ms to under 1 ms (with EXPLAIN output)
Most "slow database" problems I see are not exotic. They are the same handful of patterns: a missing index on a foreign key, a function wrapped around a column, an OFFSET that reads 200,000 rows to show 20.
I ran each pattern below on a 1,000,000-row orders table in PostgreSQL 16.6, captured EXPLAIN (ANALYZE, BUFFERS) before and after the fix, and kept the numbers as they came out. Your timings will differ - the shape of the plan is what matters.
| Fix | Before | After |
|---|---|---|
| Index the foreign key | 67.7 ms | 0.10 ms |
| Composite index (equality first, then sort column) | 80.0 ms | 0.20 ms |
| Expression index for lower(email) | 14.2 ms | 0.07 ms |
| Rewrite date(created_at) = … as a range | 100.2 ms | 0.31 ms |
| Partial index for the hot slice | 82.3 ms | 0.15 ms |
| Covering index ( INCLUDE ) | 41.2 ms | 3.9 ms |
| Keyset pagination instead of OFFSET | 28.2 ms | 0.11 ms |
| pg_trgm for ILIKE '%text%' | 168.5 ms | 1.1 ms |
| Planner estimate instead of exact count(*) | 84.1 ms | 0.07 ms |
1. Index your foreign keys
PostgreSQL creates an index for a primary key, not for a foreign key. So this innocent query:
SELECT id, status, total, created_at FROM orders WHERE customer_id = 4242;
reads the whole table:
Gather(actual time=2.554..67.674 rows=10 loops=1)
-> Parallel Seq Scan on orders(actual time=16.595..43.016 rows=3 loops=3)
Filter: (customer_id = 4242)
Rows Removed by Filter: 333330
Buffers: shared hit=96 read=15055
Execution Time: 67.727 ms
The giveaway is Rows Removed by Filter: 333330 per worker - a million rows checked to return ten. One index later:
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
Bitmap Heap Scan on orders(actual time=0.032..0.058 rows=10 loops=1)
-> Bitmap Index Scan on orders_customer_id_idx
Index Cond: (customer_id = 4242)
Buffers: shared hit=6
Execution Time: 0.104 ms
From 15,151 buffers to 6. Rule: every foreign key you filter or join on deserves an index.
2. Composite index: equality columns first, then the range/sort column
SELECT id, customer_id, total, created_at FROM orders WHERE status = 'pending' AND created_at >= '2026-06-01' ORDER BY created_at DESC LIMIT 50;
An index on (status, created_at) lets PostgreSQL jump straight to pending, read rows already in date order and stop after 50. Column order matters: equality first, then the range or sort column. 80 ms → 0.2 ms.
3. Expression index for lower() and friends
WHERE lower(email) = '…' cannot use a plain index on email. Index the expression itself:
CREATE INDEX customers_lower_email_idx ON customers (lower(email));
14.2 ms → 0.07 ms.
4. Keep the column bare (sargable queries)
-- slow: function on the column hides it from the index
WHERE date(created_at AT TIME ZONE 'UTC') = '2026-03-15'
-- fast: same rows, as a range
WHERE created_at >= '2026-03-15 00:00+00' AND created_at < '2026-03-16 00:00+00'
100.2 ms → 0.31 ms. No new index needed - just let the existing one work.
5. Partial index for the small slice you query all the time
If 5% of orders are pending and your dashboard only ever asks for those, index only them:
CREATE INDEX orders_pending_created_idx ON orders (created_at) WHERE status = 'pending';
Smaller, faster to maintain, 82 ms → 0.15 ms.
6. Covering index for index-only scans
When the index already contains every column a query needs, PostgreSQL can skip the table (Index Only Scan, Heap Fetches: 0) once the visibility map is current - i.e. after VACUUM:
CREATE INDEX orders_customer_total_idx ON orders (customer_id) INCLUDE (total);
VACUUM orders; -- updates the visibility map so the heap can be skipped
41 ms → 3.9 ms for a GROUP BY customer_id over 2,000 customers.
7. Keyset pagination instead of OFFSET
OFFSET 200000 still reads and throws away 200,000 rows. Remember the last id you showed:
SELECT id, customer_id, total FROM orders WHERE id > 200000 ORDER BY id LIMIT 20;
Same cost on page 1 and page 10,000. 28 ms → 0.11 ms.
8. ILIKE '%text%' with pg_trgm
A B-tree cannot help a leading wildcard. A trigram GIN index can:
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX orders_note_trgm_idx ON orders USING gin (note gin_trgm_ops);
168 ms → 1.1 ms.
9. Do you really need an exact count(*)?
For a "≈ 1.2M orders" badge, the planner's statistics are instant and usually within a few percent after ANALYZE:
SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE oid = 'orders'::regclass;
84 ms → 0.07 ms.
Bonus: a correctness trap, not a speed one
x NOT IN (SELECT y …) returns no rows at all if the subquery contains a single NULL. Use NOT EXISTS instead. It is not faster here - it is right.
How to find these in your own database
- Turn on
pg_stat_statementsand sort bytotal_exec_time- fix the queries that cost the most in total, not the slowest single run. - Run
EXPLAIN (ANALYZE, BUFFERS)on the top offenders. Look forSeq Scanwith a largeRows Removed by Filter, and for bigread=buffer counts. - Change one thing, re-run, compare. Keep what helps; drop indexes nobody uses (
pg_stat_user_indexes.idx_scan = 0).
I packaged this workflow - the 10 lessons with full before/after plans, a reproducible 1M-row demo dataset and 12 read-only diagnostic scripts (top queries, unused and duplicate indexes, vacuum health, blocking locks and more) - as the PostgreSQL Performance Playbook ($29, 30-day money-back guarantee).
Just want the one-page version? The free PostgreSQL Performance Checklist (PDF, $0) puts these checks on a single printable page.
If you want more SQL practice first, two well-reviewed resources from other creators on Gumroad:
- SQL Practice Problems - Professional Package - 97 challenge problems (written for SQL Server/MySQL).
- Decode PostgreSQL - how PostgreSQL works, explained without heavy code.
Disclosure: the last two are affiliate links - if you buy through them I earn a commission, at no extra cost to you.
Comments
No comments yet. Start the discussion.