Performance — EXPLAIN ANALYZE, Partitioning & Sharding

Every other topic in this course — indexes, joins, window functions — eventually raises the same question: is this actually fast, and why or why not? EXPLAIN ANALYZE is how you get a real answer instead of a guess, and partitioning/sharding are what you reach for once a single table or single database genuinely can't keep up.

A query that's fast against a small development dataset can become unacceptably slow at production scale, and there's no way to know why just by reading the SQL — you need to see the actual execution plan the database chose, and, past a certain scale, you may need to split a single huge table (or a single database) into more manageable pieces.

EXPLAIN — shows the query plan the planner would use, without running it. EXPLAIN ANALYZE — actually runs the query and shows the real plan plus actual timing/row counts per step. Partitioning — splitting one large table into smaller physical pieces (by range, list, or hash) that the planner can prune against, while it still looks like one table to queries. Sharding — splitting data across multiple separate database instances/servers, requiring the application (or a routing layer) to know which shard holds a given piece of data.

SQL performance optimization is one of the most valuable skills in database work. Understanding how the query planner works, reading execution plans, and applying the right optimization strategy can turn a 30-second query into a 30-millisecond one.

EXPLAIN command variants

EXPLAIN — shows estimated query plan (cost-based).
EXPLAIN ANALYZE — executes the query and shows actual timing + row counts.
EXPLAIN (ANALYZE, BUFFERS) — adds buffer hit/miss statistics.
EXPLAIN (ANALYZE, FORMAT JSON) — machine-readable format for tooling.

Key plan nodes to understand

Seq Scan — full table scan; unavoidable for large percentage of table reads.
Index Scan — random access via index; good for selective queries.
Index Only Scan — index contains all needed columns; best for range queries.
Bitmap Index Scan — batch index lookups; better than Index Scan for semi-selective queries.
Hash Join — builds hash table on smaller input; good for large tables without sort order.
Merge Join — merges sorted inputs; efficient when indexes provide sort order.
Nested Loop — efficient for small outer + indexed inner.

Partitioning: divides a large table into smaller physical pieces (partitions) while appearing as one table.

Range Partitioning — by date range, number range (most common).
List Partitioning — by discrete values (region, country, status).
Hash Partitioning — even distribution by hash value.

Sharding: distributes data across multiple database servers (horizontal scaling). Each server has a subset of rows.

PostgreSQL Query Planner

Parser → Analyzer → Rewriter.
Planner generates possible plans (join orders, index choices, join methods).
Cost model: cost = (seq_page_cost × pages) + (cpu_tuple_cost × rows).
Statistics: pg_statistic (column histograms, n-distinct, correlation).
Planner selects lowest-cost plan.
Executor runs the plan.

Partition Pruning

Query: SELECT * FROM orders WHERE order_date = '2024-03-15'.
Table partitioned by month: p2024_01, p2024_02, p2024_03...
Planner prunes: only p2024_03 is scanned.
11 out of 12 partitions never touched.
Dramatic performance improvement for time-range queries.

Step 1: Run EXPLAIN ANALYZE to get the actual execution plan.

Step 2: Look for the most expensive nodes (highest cost, most rows, most time).

Step 3: Check Seq Scan on large tables — candidate for indexing.

Step 4: Check estimated vs actual rows — large discrepancy = stale statistics. Run ANALYZE.

Step 5: Check Hash Batches > 1 — hash join spilling to disk. Increase work_mem.

Step 6: Apply the appropriate optimization: index, partition, rewrite query.

Step 7: Run EXPLAIN ANALYZE again to verify improvement.

Step 8: Monitor ongoing performance with pg_stat_statements.

Always EXPLAIN ANALYZE before and after optimization — measure, don't guess.
Run ANALYZE after bulk loads — stale statistics lead to poor plans.
Use pg_stat_statements to find the top slow queries by total time (not just longest single run).
Partition by the most common filter column — usually date for time-series data.
Add local indexes to each partition — indexes on the parent table are inherited but per-partition indexes can be more targeted.
Work_mem per-session for heavy sorts/hash joins: SET LOCAL work_mem = '256MB'.
VACUUM ANALYZE regularly — prevents bloat and keeps statistics current.
Don't over-engineer: profile first, optimize the actual bottleneck.
Read the actual row counts vs estimated: > 10× discrepancy means statistics are wrong.
use EXPLAIN (ANALYZE, BUFFERS) to see if queries are hitting disk (not just buffer cache).

Optimizing without measuring — creating indexes on columns that aren't the bottleneck.
Ignoring stale statistics — queries run after bulk loads without ANALYZE result in poor plans.
Misreading estimated cost as milliseconds — cost units are arbitrary (not milliseconds).
Partitioning on wrong column — partition on low-cardinality column misses pruning benefits.
Too many partitions — 10,000 partitions with tiny data each is worse than one table.
Forgetting indexes on partition key — the partition key isn't automatically indexed.
Not testing partition pruning — write EXPLAIN to verify the planner is pruning correctly.
Sharding too early — very complex; only when single node is genuinely at capacity.

Index-only scan: covering index avoids heap access; most impactful optimization.
Parallel query: enable parallel workers for seq scans (max_parallel_workers_per_gather).
Connection pooling: PgBouncer reduces connection overhead (each PostgreSQL connection is ~5MB).
Partitioning: partition pruning eliminates irrelevant data pages entirely.
pg_partman: extension to auto-create partitions by time (eliminates manual partition management).
Columnar storage: for analytics, consider Citus's built-in columnar storage or TimescaleDB compression.
Table fillfactor: CREATE TABLE ... WITH (fillfactor=80) — leaves room for HOT updates.
Materialized views: pre-compute expensive aggregations; refresh on schedule.

Restrict who can run EXPLAIN ANALYZE (which actually executes the query, including any writes wrapped in it) in production — an unrestricted ability to run arbitrary queries "just to see the plan" is functionally the same access risk as unrestricted query access itself.

Set up automated slow-query logging with a defined threshold, and review EXPLAIN ANALYZE output for anything above it regularly, not just reactively during an incident — the gap between rows planned and rows actual in the output is one of the fastest signals that planner statistics are stale and need an ANALYZE run.

Enable auto_explain: auto logs slow query plans (log_min_duration = 1000ms).
pg_stat_statements: essential for finding the actual worst queries in production.
Partition maintenance: use pg_partman for automatic partition creation.
Monitor table bloat weekly: dead_pct > 20% triggers VACUUM.
Vacuum tuning: increase autovacuum_vacuum_scale_factor for large tables.
Connection limit: max_connections = 100-200 for most workloads; use PgBouncer for pooling.
Shared buffers: set to 25% of RAM (e.g., 4GB for 16GB RAM server).

Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on a 3-table JOIN query. Identify: join method used, most expensive node, estimated vs actual rows. Create an index to improve it and verify with EXPLAIN again.
Create a partitioned orders table (by quarter). Insert 100K rows. Run EXPLAIN on a query with a date range. Verify partition pruning is happening (only some partitions scanned).
Install pg_stat_statements extension. Run 20 different queries. Then query pg_stat_statements to find the top 5 slowest by mean_exec_time.
Find a table with high dead_pct (or simulate with many UPDATEs). Run VACUUM ANALYZE. Observe the improvement in table size and statistics.

EXPLAIN ANALYZE: actual timing + row counts; BUFFERS adds cache hit/miss stats.
Key plan nodes: Seq Scan (no index), Index Scan (random), Index Only Scan (best), Hash Join (large tables).
Estimated vs actual rows: > 10× discrepancy = stale statistics → run ANALYZE.
Hash Batches > 1 = spilling to disk → increase work_mem.
Partitioning: Range (date), List (status/region), Hash (even distribution). Enables partition pruning.
Sharding: physical distribution across servers — last resort for horizontal scaling.
pg_stat_statements: find slow queries in production by total_exec_time.
VACUUM ANALYZE: prevents bloat and keeps statistics current.

Performance — EXPLAIN ANALYZE, Partitioning & Sharding

EXPLAIN command variants

Key plan nodes to understand

PostgreSQL Query Planner

Partition Pruning

Practice quiz

Next Step