Rewrite patterns that make queries fast

conceptThe concept

Sargable predicates compare a bare column with a value: created_at >= $1, not date(created_at) = $1; price > 50, not price * 2 > 100. Wrapping the column hides it from plain indexes and from its statistics.

Keyset pagination remembers the last row seen: WHERE (created_at, id) < ($last_ts, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20. With an index on (created_at, id) every page costs the same, while OFFSET 100000 reads and discards 100,000 rows.

N+1: fetching a list and then one query per item. Replace with a join, = ANY($1::int[]) or LATERAL. OR across columns often prevents a single index scan; UNION of two indexed queries, or BitmapOr over two indexes, fixes it. Exact counts of big tables are expensive in MVCC; for UI badges use reltuples or an EXPLAIN estimate.

whyWhy it matters

Indexes cannot help a query that hides its columns inside functions or asks for page 5,000 by offset. These rewrites are cheap, safe and usually the biggest wins in application code reviews.

Use caseReal-world scenario

The admin "all orders" screen slows down linearly as users page further. Keyset pagination makes page 500 as fast as page 1.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE INDEX orders_created_idx ON orders (created_at);
EXPLAIN SELECT count(*) FROM orders WHERE created_at::date = '2025-06-01';
EXPLAIN SELECT count(*) FROM orders WHERE created_at >= '2025-06-01' AND created_at < '2025-06-02';
ROLLBACK;

BEGIN;
CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, created_at FROM orders ORDER BY created_at DESC, id DESC OFFSET 8000 LIMIT 20;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, created_at FROM orders
WHERE (created_at, id) < ('2024-04-21 12:00+00', 1576)
ORDER BY created_at DESC, id DESC LIMIT 20;
ROLLBACK;

-- one query for many ids instead of one query per id
SELECT o.id, o.status, o.total
FROM orders o
WHERE o.id = ANY ('{11,25,37,98,1203}'::int[])
ORDER BY o.id;

SELECT reltuples::bigint AS estimated_rows FROM pg_class WHERE oid = 'events'::regclass;
SELECT pgm.estimate('SELECT * FROM events WHERE kind = ''search''') AS planner_estimate,
       (SELECT count(*) FROM events WHERE kind = 'search') AS exact;

Avoid / preferDo this, not that

SELECT count(*) FROM orders WHERE extract(year FROM created_at) = 2025;

SELECT count(*) FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

SELECT * FROM orders
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;

PitfallsCommon mistakes

• Applying functions or casts to indexed columns in WHERE.
• Using OFFSET for deep pagination or infinite scroll.
• Running SELECT count(*) on a large table to show "1,234,567 results" on every page view.

LimitsWhen not to use it

Keyset pagination cannot jump to page 500 directly. For admin screens that need arbitrary page numbers on small result sets, OFFSET is fine.

SecuritySecurity notes

Large user-supplied OFFSET or LIMIT values are a cheap denial-of-service. Cap page sizes and depth on the server side.

ChallengeTry it yourself

SELECT id, created_at
FROM orders
WHERE (created_at, id) < (SELECT created_at, id FROM orders WHERE id = 5000)
ORDER BY created_at DESC, id DESC
LIMIT 5;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• pg_stat_statements
• auto_explain