Optimizing PostgreSQL chevron_right PostgreSQL Statistics & The Planner: How Data Drives Query Plans

PostgreSQL Statistics & The Planner: How Data Drives Query Plans

calendar_today schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

  • Explain how PostgreSQL collects and uses statistics for query planning
  • Use the ANALYZE command and interpret the pg_statistic and pg_stats tables
  • Identify and fix issues caused by outdated or inaccurate statistics

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered why PostgreSQL sometimes makes a bad choice for a query plan, even when you have the right indexes? Today, you’ll learn how the database “sees” your data and how you can help it make better decisions!

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Completed Days 1-3, basic understanding of query plans and I/O
  • Materials Needed: PostgreSQL database, SQL client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. What are statistics in PostgreSQL?
  2. How the planner uses statistics to choose query plans
  3. Using ANALYZE, pg_statistic, and pg_stats
  4. How bad statistics lead to bad plans
  5. Hands-on practice and exercises
  6. Common pitfalls and troubleshooting

Success Criteria:

By the end of this session, you should be able to update and interpret statistics, and recognize when statistics are causing poor query plans.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What Are Statistics in PostgreSQL?

Statistics are metadata about your tables: how many rows, value distributions, most common values, and more. The planner uses these to estimate how many rows a query will process.

Real-World Analogy: Imagine planning a trip with a map that shows traffic patterns. If your map is outdated, you might get stuck in traffic. PostgreSQL’s planner relies on up-to-date statistics to avoid “traffic jams” in query plans.

Why This Matters:

  • Accurate statistics help the planner choose the best plan
  • Outdated or missing statistics can lead to slow queries

The ANALYZE Command

  • Collects statistics for a table or database
  • Should be run after large data changes
  • Autovacuum usually runs it automatically, but you can run it manually

The pg_statistic and pg_stats Tables

  • pg_statistic: Internal table with detailed stats (not user-friendly)
  • pg_stats: View for human-readable statistics (one row per column)
  • Shows histograms, most common values (MCV), null fraction, and more

Key Terminology

  • Histogram: Distribution of values in a column
  • MCV (Most Common Values): Most frequent values in a column
  • Correlation: How ordered the data is (affects index usage)
  • Autovacuum: Background process that runs ANALYZE and VACUUM

4. Detailed Explanation and Examples (25-30 minutes)

How the Planner Uses Statistics

The planner estimates how many rows each step will process. It uses statistics to decide:

  • Whether to use an index or a sequential scan
  • Which join method to use
  • How much memory to allocate for sorts and hashes

Practical Applications

  • Run ANALYZE after bulk inserts, updates, or deletes
  • Check pg_stats to see if statistics match your data
  • Use statistics to diagnose bad query plans

Common Misconceptions

  1. Misconception: “Statistics are only about row counts.” Reality: PostgreSQL tracks value distributions, nulls, and more.
  2. Misconception: “Autovacuum always keeps stats up to date.” Reality: Heavy data churn or large bulk loads may require manual ANALYZE.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Viewing Statistics

-- See stats for a table
SELECT * FROM pg_stats WHERE tablename = 'users';

Explanation:

  • Shows null fraction, most common values, histogram, and more for each column

Example 2: Running ANALYZE

-- Update statistics for a table
ANALYZE users;
  • Should be run after significant data changes

Example 3: Bad Estimates from Outdated Stats

-- Change a lot of data
UPDATE users SET status = 'inactive';
-- Run a query and check the plan
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';
-- Now update stats
ANALYZE users;
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';
  • The first plan may have bad row estimates; after ANALYZE, estimates should improve

Debugging Demonstration

Show how to spot bad estimates:

-- Look for big differences between estimated and actual rows
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%@gmail.com';

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: View and interpret statistics for a table

Instructions:

  1. Pick a table in your database
  2. Run SELECT * FROM pg_stats WHERE tablename = 'your_table';
  3. Note the null fraction, most common values, and histogram for key columns

Starter Code:

SELECT * FROM pg_stats WHERE tablename = 'your_table';

Solution:

-- Example output (truncated)
attname | null_frac | n_distinct | most_common_vals | histogram_bounds
--------+-----------+------------+------------------+-----------------
status  | 0.01      | -0.5       | {active,inactive}| {active,deleted}

Exercise 2: Intermediate Challenge (15 minutes)

Objective: See how changing data affects statistics and query plans

Requirements:

  • Update a large portion of a table
  • Run a query and check the plan
  • Run ANALYZE and check the plan again

Hints:

  • Use UPDATE or INSERT to change data
  • Look for changes in estimated vs. actual rows

Exercise 3: Creative Application (15 minutes)

Objective: Experiment with statistics and planner decisions

Challenge:

  • Create a table with skewed data (e.g., 90% of rows have the same value)
  • Run queries and observe planner estimates
  • Try CREATE STATISTICS for multi-column stats (PostgreSQL 10+)

Extension Ideas:

  • Explore how correlation affects index usage
  • Try with different data distributions

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Not running ANALYZE after bulk data changes Cause: Planner uses outdated statistics Solution: Run ANALYZE manually after large changes

  2. Problem: Misinterpreting statistics in pg_stats Cause: Not understanding MCVs, histograms, or correlation Solution: Review documentation and experiment with sample data

Debugging Tips

  • Always check estimated vs. actual rows in query plans
  • Use ANALYZE to refresh stats after big data changes
  • Use pg_stats for a human-readable view of statistics

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Monitor and update statistics regularly
  • Use CREATE STATISTICS for multi-column dependencies (PostgreSQL 10+)
  • Document when and why you run manual ANALYZE

Next Steps in Your Learning Journey

  • Learn about planner settings and overrides (see Day 5)
  • Explore advanced statistics features
  • Practice with real-world data and query plans

9. Summary and Review (5 minutes)

Key Takeaways

  1. Statistics drive the planner’s decisions
  2. Outdated or inaccurate stats can cause bad query plans
  3. Use ANALYZE and pg_stats to keep and check stats

Quick Review Questions

  1. What does the ANALYZE command do?
  2. How can you view statistics for a table?
  3. Why do bad statistics lead to bad query plans?

For Instructors

Teaching Tips

  • Demonstrate with tables before and after running ANALYZE
  • Show how planner estimates change with data distribution
  • Use real-world examples of bad plans caused by stats

Assessment Ideas

  • Have students interpret pg_stats output
  • Ask students to fix a bad plan by updating statistics
  • Peer review: students explain how stats affect a sample plan

Adaptation Notes

  • For beginners: focus on running ANALYZE and reading pg_stats
  • For advanced students: include multi-column stats and correlation
  • Online: use screen sharing to show live stats and plan changes