EXPLAIN & Query Optimization

Reading EXPLAIN

EXPLAIN SELECT * FROM students WHERE department = 'CSE';
EXPLAIN ANALYZE SELECT …;                    -- actually runs it, shows real timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT …;

EXPLAIN alone shows the estimated plan and cost; ANALYZE actually executes the statement (wrap a write in a transaction and roll back if you do not want it to take effect) and reports real row counts and timings.

A sample plan:

Seq Scan on students  (cost=0.00..18.50 rows=200 width=64) (actual time=0.02..0.45 rows=210 loops=1)
  Filter: (department = 'CSE'::text)
  Rows Removed by Filter: 790
Planning Time: 0.15 ms
Execution Time: 0.52 ms

What to look at

Node	Meaning
Seq Scan	Full table scan
Index Scan	Index lookup, then fetch the row from the table
Index Only Scan	Answered entirely from the index — fastest
Bitmap Heap Scan	Index narrows candidates, then a batch fetch
Nested Loop / Hash Join / Merge Join	The three join strategies
Sort	An explicit sort step, often feeding a GROUP BY or ORDER BY

Rows estimated vs actual is the single most useful number: a large gap means the planner's statistics are stale, and ANALYZE table_name usually fixes it.

Cost is in arbitrary units (roughly: sequential page reads), not milliseconds — use ANALYZE's actual timings to judge real speed.

Common problems and fixes

-- Seq Scan on a big table filtered by an unindexed column
CREATE INDEX idx_dept ON students(department);

-- Rows Removed by Filter is huge → the WHERE isn't sargable
-- before
WHERE extract(year FROM admitted_on) = 2024
-- after
WHERE admitted_on >= '2024-01-01' AND admitted_on < '2025-01-01'

-- planner picked a bad join order or method
ANALYZE students;   -- refresh statistics first

pg_stat_statements

The single most useful extension for finding what is actually slow across your whole workload, not just one query you suspect:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

SELECT pg_stat_statements_reset();

Autovacuum and ANALYZE

An out-of-date planner is a common, invisible cause of bad plans:

SELECT relname, last_autoanalyze, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

ANALYZE students;              -- refresh statistics for one table
ANALYZE;                       -- refresh for the whole database

If autovacuum is falling behind on a busy table, tune it per table rather than globally:

ALTER TABLE students SET (autovacuum_vacuum_scale_factor = 0.05);

Optimisation checklist

1. Index what you filter, join and sort on — this fixes most slow queries.
2. Select only the columns you need, so index-only scans become possible.
3. Keep WHERE sargable — no functions wrapped around indexed columns.
4. Run ANALYZE after large data changes so the planner has fresh numbers.
5. Use EXISTS rather than IN for large correlated subqueries.
6. Batch writes — one large INSERT/COPY beats many small ones.
7. Watch n_dead_tup and make sure autovacuum keeps up.
8. Use pg_stat_statements to find what to optimise next, rather than guessing.

Server-level tuning

SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;

On a dedicated server, a common starting point is shared_buffers at 25% of RAM, effective_cache_size at 50–75% of RAM, and work_mem sized per expected concurrent sort/hash operation (too high risks memory exhaustion under load, since it applies per operation, per connection). Use pgtune or your platform's defaults as a starting point, then measure.