Query tuning and execution plans

How to read EXPLAIN output, which scan and join strategies matter, why stale statistics ruin a plan, and how to write predicates an index can actually use.

Reading a plan

The planner is not guessing at random: it costs alternatives using statistics and picks the cheapest. Reading its plan is the difference between tuning the real bottleneck and tuning what you assume is slow.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= now() - interval '30 days'
GROUP BY c.name
ORDER BY spent DESC
LIMIT 10;

-- Limit  (cost=1820.4..1820.5 rows=10) (actual time=41.2..41.3 rows=10)
--   -> Sort  (cost=1820.4..1822.9 rows=1000) (actual time=41.2..41.2 rows=10)
--         Sort Key: (sum(o.total)) DESC
--         -> HashAggregate  (rows=1000) (actual time=30.1..38.4 rows=812)
--               Group Key: c.name
--               -> Hash Join  (rows=10000) (actual time=3.0..19.9 rows=9412)
--                     Hash Cond: (o.customer_id = c.id)
--                     -> Seq Scan on orders o  (actual rows=9412)
--                           Filter: (placed_at >= (now() - '30 days'::interval))
--                     -> Hash  (actual rows=5000)
--                           -> Seq Scan on customers c

Compare estimated rows with actual rows. A large gap means the planner chose a strategy for a table that does not exist, and everything downstream of that estimate is suspect.

Predicates an index can use

-- not sargable: the column is wrapped in a function, so no seek is possible
SELECT * FROM orders WHERE date_trunc('day', placed_at) = '2026-09-01';

-- sargable: a half-open range on the bare column
SELECT * FROM orders
WHERE placed_at >= '2026-09-01' AND placed_at < '2026-09-02';

-- not sargable: a leading wildcard cannot use a B-tree index
SELECT * FROM products WHERE name LIKE '%lamp%';

-- sargable: prefix match, plus a functional index for case-insensitive search
SELECT * FROM products WHERE name LIKE 'lamp%';
CREATE INDEX idx_products_lower_name ON products (lower(name));
SELECT * FROM products WHERE lower(name) = 'desk lamp';

-- arithmetic belongs on the constant side
SELECT * FROM products WHERE price > 100 / 1.2;
SELECT * FROM users WHERE id = 42;   -- id is an integer; do not pass '42'

• Leave the column bare on the left of the comparison and push every transformation onto the constant.
• OR across different columns often defeats a single index seek; a UNION ALL of two indexed queries can be faster.
• Select only the columns you need: a covering index can answer the query without touching the table at all.
• LIMIT only helps when the plan can stop early — with ORDER BY on an unindexed column the engine still sorts every row first.

Statistics and join strategies

-- the planner decides with statistics, so keep them fresh and specific
ANALYZE orders;
CREATE STATISTICS orders_customer_status (dependencies)
  ON customer_id, status FROM orders;

-- see how many rows the planner believes a predicate matches
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'paid';

-- disable a strategy only to test a hypothesis, never in production
SET enable_seqscan = off;
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
RESET enable_seqscan;

• Autovacuum keeps statistics reasonably fresh on healthy systems; bulk loads and migrations can outrun it, so run ANALYZE yourself afterwards.
• A partial index (WHERE status = 'open') is far smaller and faster when almost every query only ever wants the small subset.
• Change one thing, measure again, and keep the before-and-after numbers — tuning without measurements is guesswork with extra steps.

FAQ

Related

Subqueries, CTEs and window functions Transactions and isolation levels

Last refreshed 2026-09-18.