9 PostgreSQL fixes that took real queries from ~100 ms to under 1 ms (with EXPLAIN output)
DEV Community

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_statements and sort by total_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 for Seq Scan with a large Rows Removed by Filter, and for big read= 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.

Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.