Query Planning and Statistics

1. Introduction

The query planner is the brain of PostgreSQL's execution engine. It evaluates multiple execution strategies — Seq Scan, Index Scan, Hash Join, Merge Join, and more — then selects the cheapest path based on statistics stored in the pg_statistic catalog. These statistics include row counts, value distributions, most-common-values (MCV), and histograms. When stats are stale or missing, the planner makes poor decisions, leading to full table scans on indexed columns or wrong join orders.

2. Why It Matters

Accurate statistics are the single most important factor in query performance tuning. A well-indexed table with outdated stats can perform worse than no index at all, because the planner may choose an index scan that reads 90% of the table row-by-row instead of a faster sequential scan. Understanding how statistics are collected, stored, and used lets you diagnose mysterious slow queries that look correct but execute poorly.

3. Real-World Analogy

Imagine a GPS navigation app that uses traffic data from last week to suggest routes. If a highway was closed yesterday, the app still recommends it because its data is stale. Similarly, if you bulk-insert 10 million rows but haven't run ANALYZE, the planner thinks the table is still small and picks a plan optimized for the old row count.

4. How It Works

PostgreSQL collects statistics via the autovacuum daemon (which also runs ANALYZE) or manually via the ANALYZE command. Key statistics stored in pg_statistic include:

• reltuples / relpages: Estimated row count and page count from pg_class.
• n_distinct: Number of unique values in a column. If negative, it represents the fraction of unique rows.
• most_common_vals (MCV): The most frequently occurring values and their frequencies.
• histogram_bounds: Equi-height histogram splitting column values into buckets for selectivity estimation.
• null_frac: Fraction of NULL values in the column.

The planner uses these to compute selectivity — the fraction of rows a WHERE clause will return — then estimates the cost of each candidate plan using CPU and I/O cost constants (cpu_tuple_cost, random_page_cost, seq_page_cost).

5. Internal Architecture

The planning pipeline follows these stages:

1. Parse & Rewrite: SQL text is parsed into a query tree and rewritten by rules.
2. Planner/Optimizer: Generates candidate paths (Seq Scan, Index Scan, various joins) using statistics from pg_statistic and pg_class.
3. Cost Estimation: Each path is assigned a cost in abstract units (CPU + I/O).
4. Cheapest Path Selection: The lowest-cost plan wins and is compiled into an execution plan tree.
5. Execution: The executor walks the plan tree, fetching rows through each node.

The default_statistics_target (default: 100) controls how many histogram buckets and MCV entries are stored. Increasing it (up to 10,000) gives the planner better data at the cost of slower ANALYZE runs.

6. Visual Explanation

The diagram illustrates how the planner reads statistics from pg_statistic, generates candidate plans, estimates cost for each, and selects the cheapest execution path.

7. Practical Example

Check current statistics and force a re-analyze:

If reltuples shows 1,000 but the table actually has 10 million rows, the planner will wrongly prefer Seq Scan over Index Scan for selective queries.

8. Common Mistakes

• Never running ANALYZE after bulk loads: The planner uses stale row counts and picks suboptimal plans.
• Ignoring autovacuum tuning: Default autovacuum thresholds may be too high for tables with frequent small updates.
• Using default statistics_target for skewed columns: Columns with highly uneven distributions need higher statistics targets for accurate histograms.
• Trusting EXPLAIN without ANALYZE: EXPLAIN alone shows estimated costs; EXPLAIN (ANALYZE) reveals the actual vs estimated discrepancy.

9. Quick Quiz

10. Scenario-Based Challenge

You bulk-imported 50 million rows into events using COPY. Queries filtering by created_at now take 45 seconds despite having a B-tree index. Use EXPLAIN (ANALYZE) to diagnose the plan choice, check pg_class.reltuples, and fix the issue. Verify the query drops below 100ms.

11. Debugging Exercise

The following query performs poorly despite an index on user_id:

Root cause: reltuples was stale at 500K while the table had 10M rows. The planner underestimated selectivity and chose Seq Scan.

12. Interview Questions

• Q: How does PostgreSQL's query planner decide between Seq Scan and Index Scan?
  A: It estimates the cost using statistics — row count, selectivity from histograms and MCV, I/O cost constants. If the estimated fraction of matching rows is high (typically >15-20%), Seq Scan is cheaper due to sequential I/O.
• Q: What is the purpose of the default_statistics_target setting?
  A: It controls the number of histogram buckets and MCV entries stored per column. Higher values give the planner more accurate selectivity estimates at the cost of more ANALYZE time and pg_statistic storage.
• Q: When would you manually run ANALYZE instead of relying on autovacuum?
  A: After large COPY/bulk INSERT operations, after creating a new index, or when EXPLAIN shows wildly wrong row estimates. Autovacuum's thresholds (default: 50 + 20% of table size) may not trigger fast enough for rapid data loads.

13. Production Considerations

• Autovacuum tuning: Set autovacuum_analyze_scale_factor = 0.02 for large tables to trigger ANALYZE more frequently.
• Statistics target per column: Use ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 for high-cardinality columns used in critical WHERE clauses.
• pg_stat_user_tables: Monitor last_analyze and last_autoanalyze timestamps to detect stale statistics.
• plan_cache_mode: For prepared statements, set plan_cache_mode = force_custom_plan if generic plans perform poorly due to parameter-sensitive statistics.