PostgreSQL Query Plan Analysis: EXPLAIN Deep Dive

Tip: EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts WHERE category_id = 1;

Shows actual execution time and buffer usage.

Gotcha: Seq Scan vs Index Scan

• Seq Scan — reads every row (bad for large tables)
• Index Scan — uses index (good for selective queries)
• Bitmap Index Scan — uses index, then fetches rows (good for moderate selectivity)

Tip: Nested Loop vs Hash Join

• Nested Loop — good for small inner table
• Hash Join — good for large tables
• Merge Join — good for pre-sorted data

Gotcha: Rows Estimate vs Actual

If rows estimate is way off from actual, run ANALYZE to update statistics.

Tip: Cost Breakdown

cost=0.00..1234.56

First number = startup cost, second = total cost. High startup cost means slow to return first row.

Gotcha: work_mem Affects Sort Strategy

Low work_mem forces disk-based sorts. Increase for better performance on large sorts.

Tip: EXPLAIN (ANALYZE, BUFFERS) Is Your Best Friend

For query debugging, always use EXPLAIN (ANALYZE, BUFFERS) instead of plain EXPLAIN. The BUFFERS option shows hit/miss rates for every node, revealing whether your indexes are actually in memory.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

I've developed a rule of thumb for PostgreSQL index design: start with indexes on foreign key columns, add indexes for the WHERE clauses in your top 10 slowest queries (identified via pg_stat_statements), and stop. Most tables need 5-10 indexes at most. More indexes slow down writes without improving read performance. I've seen tables with 30+ indexes where the write performance was terrible and query performance wasn't noticeably better than with 10 well-chosen indexes.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)