Skip to content
Tutorials Center
My learning

Topics

Lesson 63 of 90 ยท Performance Tuning

Query Tuning Patterns

Practical PostgreSQL query tuning patterns: sargable predicates, avoiding SELECT *, keyset pagination, EXISTS vs IN, batching, N+1 queries, CTEs and COUNT(*).

Plans, statistics and configuration explain why a query is slow. Most day-to-day tuning, though, is pattern recognition: you read a query, notice a shape you have seen before, and rewrite it into one the planner handles well. This lesson collects the patterns that matter most in PostgreSQL, from predicates that defeat indexes to paging that gets slower with every page.

The general ideas are not specific to PostgreSQL, and you can see the portable ones working in Writing Fast Queries. Here the live examples run on SQLite so you can compare plans and results yourself; the PostgreSQL-specific behavior (planner details, EXPLAIN output, CTE handling) is shown in read-only blocks.

Keep predicates sargable

A predicate is sargable when the planner can turn it into an index condition. Wrapping the indexed column in a function or calculation hides it:

SQL
-- Slow: no index on created_at can be used
SELECT * FROM orders WHERE date_trunc('day', created_at) = '2025-03-01';
SELECT * FROM orders WHERE created_at::date = '2025-03-01';

-- Fast: a range on the bare column
SELECT * FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-03-02';

More cases that hide an index, and their fixes:

Hides the index Better
WHERE lower(email) = '[email protected]' Create an expression index on lower(email), or use citext
WHERE price * 1.2 > 100 WHERE price > 100 / 1.2
WHERE id + 1 = 10 WHERE id = 9
WHERE name LIKE '%son' A trigram index (pg_trgm) or full-text search
WHERE coalesce(status, '') = 'x' WHERE status = 'x' (NULL never equals 'x' anyway)
WHERE text_col = 42 (implicit cast of the column) Compare with the right type: WHERE text_col = '42'

PostgreSQL also has one more way to defeat an index: a data-type mismatch that forces a cast of the column, for example comparing an integer column with a numeric parameter in some drivers. Check the plan's Filter: and Index Cond: lines; a predicate that appears under Filter: instead of Index Cond: was not used for searching.

An expression index makes a function call sargable, as long as the query uses exactly the same expression. You will see how in Partial and Expression Indexes.

Select only what you need

SELECT * costs more than it looks:

  • It prevents index-only scans, because the index rarely contains every column.
  • It moves more bytes: wide columns (large text, JSONB) are stored out of line in TOAST tables, and each one fetched costs extra reads.
  • It makes sorts and hashes bigger, so they spill sooner (see Reading Slow Plans).
  • It breaks when columns are added, renamed or reordered.

Name the columns the caller uses. The same goes for rows: add LIMIT for anything displayed, and aggregate in the database instead of fetching rows to sum them in the application.

Four habits that fix most queries

Bare columns Keep indexed columns out of functions.
Fewer columns Name what you need.
Keyset paging Seek past the last row.
Fewer round trips Batch instead of looping.

Measure with EXPLAIN ANALYZE before and after.

1 / 2

Keyset pagination

OFFSET paging looks harmless and gets slower with every page, because the database must produce and discard every skipped row:

SQL
SELECT id, title FROM posts ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;   -- reads 100,020 rows, returns 20

Keyset pagination (also called the seek method) remembers where the last page ended and asks for the rows after that point. With an index on (created_at, id), each page costs the same, whether it is page 2 or page 5,000:

SQL
SELECT id, title FROM posts
WHERE (created_at, id) < ($1, $2)       -- last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

The row comparison (a, b) < (x, y) is standard SQL and works in PostgreSQL and SQLite. The id tie-breaker matters: without it, rows sharing a created_at could be skipped or repeated across pages. The trade-off is that keyset paging cannot jump to "page 400" directly; it suits "next page" and infinite scroll, and the page number itself is rarely needed.

Try both on 50,000 rows. The results are identical; the plans show the work:

SQL OFFSET versus keyset pagination

Press Run (or Ctrl + Enter) to see the result here.

(The OFFSET query and the keyset query return the same twenty rows, starting at id 19981. The EXPLAIN QUERY PLAN shows SEARCH posts USING INDEX posts_created_idx (created_at>?) and no sort step: SQLite jumps straight to the right place in the index and reads on from there.)

EXISTS, IN and NOT IN

For "does a matching row exist" questions, three forms are common. In PostgreSQL, EXISTS and IN (subquery) are usually planned the same way (as a semi-join), so choose the one that reads best. The real traps are elsewhere:

  • NOT IN with NULLs. If the subquery returns even one NULL, x NOT IN (...) is never true, and you get zero rows. NOT EXISTS has no such trap, and PostgreSQL plans it as an efficient anti-join. NOT IN against a subquery is also often planned much worse (a "hashed SubPlan" that falls back to repeated scans when the set is too big for work_mem).
  • Joins that multiply rows. Using JOIN just to filter duplicates rows when there are several matches, then people "fix" it with DISTINCT. A semi-join (EXISTS) never multiplies rows.
  • Correlated scalar subqueries in the select list run once per row. Rewrite as a join or a lateral join when the outer set is large.
SQL NOT IN versus NOT EXISTS with a NULL

Press Run (or Ctrl + Enter) to see the result here.

The first query returns nothing (the NULL poisons NOT IN), the second returns Ben, the join yields 3 rows for 2 customers, and EXISTS counts the 2 customers correctly.

Batching and the N+1 problem

The most expensive query is often the one you run ten thousand times. The N+1 pattern is one query to fetch a list, followed by one query per item to fetch its details:

TEXT
SELECT id FROM authors LIMIT 100;            -- 1 query
SELECT * FROM books WHERE author_id = 1;     -- then N more...
SELECT * FROM books WHERE author_id = 2;
...

Each round trip costs network latency and a parse/plan/execute cycle, even when the query itself is fast. Fix it with one joined query, or one batched query with an array parameter:

SQL
SELECT * FROM books WHERE author_id = ANY($1);   -- $1 is an array of ids

Writes batch the same way. Inserting 1,000 rows one statement at a time means 1,000 round trips and 1,000 commits (each commit waits for a WAL flush) unless they share a transaction. Use multi-row INSERT ... VALUES (...), (...), or COPY for bulk loads (Bulk Loading and COPY).

How do you detect N+1 in production? Look in pg_stat_statements: one very cheap statement with an enormous calls count (and a large total time) is the signature. Application-side, ORMs have eager-loading options and tools that flag repeated queries per request.

Here batching is easy to see by counting round trips, which is what costs time in a real application:

SQL One query per row or one for all

Press Run (or Ctrl + Enter) to see the result here.

CTEs and optimization fences

Before PostgreSQL 12 every CTE (WITH query) was an optimization fence: it was always computed once, on its own, with no conditions pushed into it. Since version 12, a non-recursive CTE that is used once and has no side effects is inlined into the outer query like a subquery, so filters pushed from outside can use indexes. You can still choose:

SQL
WITH recent AS MATERIALIZED (        -- force a separate step, computed once
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT ... FROM recent r JOIN ... ;

WITH recent AS NOT MATERIALIZED (    -- force inlining even if used twice
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT ... ;

Use MATERIALIZED when the CTE is expensive and referenced several times, or when you deliberately want a fence to stop a bad plan. CTEs that contain INSERT/UPDATE/DELETE or are referenced multiple times are materialized by default. Details are in CTEs, Materialization and Recursive Queries.

The cost of COUNT(*)

SELECT COUNT(*) FROM big_table is slow in PostgreSQL, and not by accident. Because of MVCC, each row version has to be checked for visibility to your transaction, so there is no stored "row count" to read. The plan reads the whole table (or the smallest index, with an index-only scan if the visibility map is up to date).

Ways to cope, from exact to cheap:

  • Exact and fast enough: make sure the table is regularly vacuumed so index-only scans can work, and count with a filter an index supports.
  • Approximate: SELECT reltuples::bigint FROM pg_class WHERE relname = 'big_table' returns the planner's estimate in microseconds. It is as fresh as the last ANALYZE or autovacuum.
  • Maintained counter: keep a summary table updated by a trigger or the application (it adds write contention, so use it only where the count is shown constantly).
  • Redesign: most UIs do not need "page 1 of 8,247"; "next" and "previous" suffice with keyset pagination.

Counting rows that match a selective filter is cheap when an index covers the filter, and EXISTS is much cheaper than COUNT(*) > 0 because it stops at the first match.

Do not guess which pattern is slow. Run EXPLAIN (ANALYZE, BUFFERS) before and after the rewrite on realistic data, and keep the change only if the numbers improve. A rewrite that helps on 100 rows can hurt on 100 million.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which condition can use a plain B-tree index on created_at?

    Only the range compares the bare column with constants. The others wrap the column in a cast or function, so they cannot be used as index conditions.
  2. 2. Why does keyset pagination stay fast on deep pages?

    With an index on the ordering columns, the database jumps to the position after the last row of the previous page. OFFSET has to produce every skipped row first.
  3. 3. A subquery in NOT IN returns one NULL. What does the query return?

    x NOT IN (..., NULL) evaluates to unknown for every x not in the list, so no row passes. NOT EXISTS avoids the trap.
  4. 4. In PostgreSQL 12 and later, what happens to a simple non-recursive CTE used once?

    Inlining lets conditions from the outer query reach the CTE's tables. MATERIALIZED forces the old fence behavior.
  5. 5. What is the cheapest way to get an approximate row count of a huge table?

    pg_class.reltuples is the planner's estimate from the last analyze or vacuum, returned instantly. COUNT variants read the table or an index.

Summary

  • Keep indexed columns bare in predicates; move calculations to the constant side or build an expression index.
  • Select only needed columns and rows; SELECT * blocks index-only scans and inflates sorts.
  • Use keyset pagination with a tie-breaker instead of large OFFSET values.
  • Prefer NOT EXISTS to NOT IN against subqueries; EXISTS avoids duplicate rows from filtering joins.
  • Fix N+1 patterns with joins or = ANY($1), and batch writes into transactions or multi-row inserts.
  • CTEs are inlined by default since PostgreSQL 12; use MATERIALIZED to force a fence.
  • COUNT(*) is not free under MVCC; estimate with reltuples or redesign the page.

Next: Finding Slow Queries with pg_stat_statements.