PostgreSQL SQL Best Practices: Write Queries That Scale, and Read EXPLAIN Like a Pro

Query performance problems in PostgreSQL are rarely exotic. They are almost always the same handful of patterns: SELECT * on a wide table, a function call on an indexed column, an OR that prevents index usage, an implicit type cast that silently disables a filter. This post collects the rules that prevent 90% of these problems, then walks through a real EXPLAIN (ANALYZE, BUFFERS) output line by line so you can see what the planner is telling you.

Rule 1: Never SELECT * in Production

SELECT * reads every column, including TOAST-expanded values, large JSONB columns, and bytea blobs you do not need. On a table with 40 columns where you only use 3, you are reading 13× more data than necessary from disk into memory. This is not a style preference — it directly affects I/O, memory, and network transfer. Name the columns you need:

-- Bad: reads 40 columns, 200 bytes per row, 2 MB for 10,000 rows
SELECT * FROM orders WHERE customer_id = 42;

-- Good: reads 3 columns, 24 bytes per row, 240 KB for 10,000 rows
SELECT id, status, total FROM orders WHERE customer_id = 42;

Rule 2: Do Not Wrap Indexed Columns in Functions

An index on created_at cannot be used if you filter on DATE(created_at). The function is a black box to the planner — it cannot match the index to the transformed expression. Rewrite the filter to use a range:

-- Bad: index on created_at is unusable; full table scan
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';

-- Good: index on created_at is used; range scan
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';

If you genuinely need to filter on a transformed expression often, create a functional index: CREATE INDEX idx_orders_date ON orders (DATE(created_at)). But usually, rewriting the filter is simpler and more flexible.

Rule 3: OR Is Often an Index Killer

PostgreSQL can use an index for a = 1 OR b = 2 via a BitmapOr, but only if both columns are independently indexed — and even then, it is less efficient than two separate queries unioned. More commonly, OR across columns forces a sequential scan:

-- Bad: likely Seq Scan if only one of the columns is indexed
SELECT * FROM orders WHERE customer_id = 42 OR status = 'pending';

-- Good: two index scans, unioned
SELECT * FROM orders WHERE customer_id = 42
UNION
SELECT * FROM orders WHERE status = 'pending';

If the OR is on the same column, use IN instead — the planner handles it as a single index scan with multiple probe values:

-- Bad
SELECT * FROM orders WHERE status = 'pending' OR status = 'shipped' OR status = 'processing';

-- Good
SELECT * FROM orders WHERE status IN ('pending', 'shipped', 'processing');

Rule 4: Watch for Implicit Type Casts

If order_number is VARCHAR and you write WHERE order_number = 12345, PostgreSQL casts the integer literal to text — usually fine. But if id is BIGINT and you write WHERE id = '42', the cast direction depends on the context and can prevent index usage in some cases. Always match the column type, or cast explicitly:

-- Risky: implicit cast may or may not use the index
SELECT * FROM orders WHERE id = '42';

-- Safe: explicit, no ambiguity
SELECT * FROM orders WHERE id = 42::bigint;

Rule 5: LIMIT Without ORDER BY Is Random

SELECT * FROM orders LIMIT 10 without an ORDER BY returns whichever 10 rows the planner happens to find first. That might be the first 10 by physical page order, or it might be 10 rows from a parallel worker's chunk. It is not deterministic. Always specify an ORDER BY when you use LIMIT, and make sure the sort column has an index.

Rule 6: Use EXISTS Instead of IN for Subqueries on Large Sets

WHERE id IN (SELECT order_id FROM returns) materializes the subquery into a list and probes it. EXISTS short-circuits on the first match and is often faster when the subquery returns many rows:

-- Slower on large subqueries
SELECT * FROM orders WHERE id IN (SELECT order_id FROM returns);

-- Faster: short-circuits per row
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.id);

Reading EXPLAIN: A Complete Walkthrough

Now for the deep dive. Here is a real EXPLAIN (ANALYZE, BUFFERS) output for a query against a 10-million-row orders table:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.status, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 50;

Limit  (cost=0.56..1247.32 rows=50 width=44) (actual time=0.038..1.842 rows=50 loops=1)
  Buffers: shared hit=1247
  ->  Nested Loop  (cost=0.56..89234.11 rows=3582 width=44) (actual time=0.037..1.835 rows=50 loops=1)
        Buffers: shared hit=1247
        ->  Index Scan using idx_orders_created_at_desc on orders o  (cost=0.43..78234.50 rows=3582 width=24) (actual time=0.022..1.210 rows=50 loops=1)
              Index Cond: (created_at >= '2026-01-01'::date)
              Filter: (status = 'shipped'::text)
              Rows Removed by Filter: 142
              Buffers: shared hit=1147
        ->  Index Scan using customers_pkey on customers c  (cost=0.13..0.15 rows=1 width=28) (actual time=0.009..0.011 rows=1 loops=50)
              Index Cond: (id = o.customer_id)
              Buffers: shared hit=100
Planning Time: 0.124 ms
Execution Time: 1.876 ms

What each line tells you

• Limit — the top node. It stopped after 50 rows (the LIMIT 50). The actual time=0.038..1.842 means the first row came at 0.038 ms and the 50th at 1.842 ms. Total execution: under 2 ms. Good.
• Nested Loop — for each order row, it probes the customers table by primary key. This is efficient when the outer table returns few rows (here, 50 after the limit) and the inner table is indexed.
• Index Scan on orders — it used idx_orders_created_at_desc to satisfy both the WHERE created_at >= '2026-01-01' filter and the ORDER BY created_at DESC. No separate sort needed — the index is already sorted in the right direction.
• Filter: (status = 'shipped') — the index on created_at does not include status, so Postgres reads the row and applies the filter afterwards. Rows Removed by Filter: 142 means it had to check 192 rows (50 kept + 142 discarded) to find 50 shipped ones. If this number were 50,000, you would need a composite index on (created_at, status) or a partial index on WHERE status = 'shipped'.
• Buffers: shared hit=1247 — all 1,247 pages were found in shared buffers (RAM). Zero read means zero disk I/O. If you see shared read=800, that is 800 pages fetched from disk — a sign that either the table does not fit in cache or the query is scanning too much.
• loops=50 on the inner scan — the customers index was probed 50 times (once per order row). actual time=0.009..0.011 is per-loop, so total inner time is ~0.55 ms. Fine.

Red Flags in EXPLAIN

• Seq Scan on a large table — if the table has more than a few thousand rows and you see Seq Scan where you expected an index, check your filter. Either the column is not indexed, the filter is not sargable (wrapped in a function), or the planner estimates the scan is cheaper than the index (sometimes wrongly, due to stale statistics).
• Sort Method: external merge Disk — the sort did not fit in work_mem and spilled to disk. Increase work_mem for the session or add an index that provides the sort order.
• Rows Removed by Filter: 100000+ — the index is finding rows but most are being discarded. You need a more selective index (composite or partial).
• Hash Join with a huge hash table — if the build side has millions of rows, the hash table may not fit in work_mem and will spill to disk batches. Consider filtering earlier or adding an index to convert the Hash Join into a Nested Loop.
• Planning Time > 100 ms — the planner is spending too long choosing a plan. This usually means too many partitions, very complex views, or stale statistics causing bad cardinality estimates.

The Query That Will Not Scale

Here is a query that looks fine and will destroy performance as the table grows:

SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
OFFSET 100000 LIMIT 20;

PostgreSQL must produce and discard the first 100,000 rows before returning 20. On a 10-million-row table, page 5,000 takes seconds. The fix is keyset pagination — remember the last created_at you showed and ask for the next slice directly:

SELECT id, status, total FROM orders
WHERE customer_id = 42 AND created_at < :last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

Backed by CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC), every page costs the same — the database jumps straight to the cursor position instead of counting from the top.

Putting It All Together

The rules above are not academic. They are the difference between a query that runs in 2 ms for years and one that runs in 2 ms in dev, 200 ms in staging, and 20 seconds in production — because the table grew, the index stopped being used, and nobody noticed. PG Monitoring tracks each query's plan and latency over time, so when a query that was 2 ms suddenly becomes 200 ms because the planner switched from an Index Scan to a Seq Scan, you get an incident — not a user complaint.