Every time you execute a query in PostgreSQL, something remarkable happens behind the scenes. Your database doesn’t just blindly fetch data—it thinks, strategizes, and makes calculated decisions about the fastest way to retrieve what you need. This invisible intelligence is the query planner, and its secret weapon is a command that many developers overlook: ANALYZE.
The Planner’s Dilemma
Imagine you’re planning a road trip. You need to decide: should you take the highway or the scenic route? The highway might be faster if traffic is light, but if it’s rush hour, those winding back roads could save you time.
PostgreSQL’s query planner faces similar decisions thousands of times per second. For every query, it must choose between:
- Sequential scans (reading the entire table)
- Index scans (jumping directly to relevant rows)
- Hash joins, nested loops, or merge joins
- Parallel execution or single-threaded processing
But here’s the catch: the planner can’t see the future. It can’t execute every possible plan to find the fastest one. Instead, it relies on something more subtle—statistics about your data.
Enter ANALYZE: The Planner’s Guide
When you run the ANALYZE command, you’re essentially giving PostgreSQL a detailed map of your data landscape:
ANALYZE orders;
PostgreSQL doesn’t read every single row (that would be too slow). Instead, it takes a statistically significant sample and builds a profile:
- How many rows are in the table?
- How many distinct values exist in each column?
- What are the most common values, and how often do they appear?
- Is the data physically sorted, or randomly scattered?
These statistics are stored in the system catalog (pg_statistic) and become the foundation for every query plan.
A Tale of Two Queries
Let me show you why this matters with a real story.
The Mystery of the Slow Customer Search
A development team deployed a new feature that searched for customers by city. In their development environment with 10,000 customers, the query was instant. In production with 5 million customers, it took 30 seconds.
The query was simple:
SELECT * FROM customers WHERE city = 'Portland';
The investigation revealed something shocking: PostgreSQL was reading the entire 5-million-row table sequentially, ignoring a perfectly good index on the city column.
Why? Because the statistics were outdated. The table had been bulk-loaded, but ANALYZE had never been run. PostgreSQL still thought the table had 10,000 rows from the initial migration.
After running ANALYZE:
ANALYZE customers;
The same query dropped to 50 milliseconds. The planner now understood the data distribution and confidently chose the index scan.
What the Planner Sees
When you query pg_stats, you can peek into the planner’s understanding of your data:
SELECT attname, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE tablename = 'customers';
This reveals fascinating insights:
n_distinct: If you see n_distinct = 50 for a city column in a million-row table, the planner knows that each city appears roughly 20,000 times. This helps it estimate result sizes.
most_common_vals: Shows the top values like ['New York', 'Los Angeles', 'Chicago']. If someone searches for New York, the planner knows this will return many rows—probably too many for an index scan.
correlation: A value near 1.0 means data is physically sorted. This tells the planner that an index scan will read data sequentially from disk (fast), not randomly (slow).
The Anatomy of a Cost Estimate
Every time PostgreSQL considers a query plan, it calculates a cost. This isn’t time in milliseconds—it’s an abstract unit representing CPU and disk I/O operations.
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
You might see output like:
Index Scan using idx_customer_id on orders (cost=0.43..8.45 rows=1 width=120)
Those numbers (cost=0.43..8.45 rows=1) come directly from ANALYZE statistics:
- 0.43: Startup cost to begin the index scan
- 8.45: Total cost to retrieve all matching rows
- rows=1: Estimated number of rows that match
If the statistics say customer_id is highly unique, the planner trusts that only 1 row will match. If they’re wrong—say there are actually 100,000 rows for that ID—the chosen plan becomes disastrous.
When Statistics Go Stale
Data changes, but statistics don’t update themselves instantly. This creates a dangerous gap:
After bulk operations:
COPY orders FROM 'big_file.csv'; -- Added 2 million rows
-- Statistics still reflect old table size!
After deletions:
DELETE FROM logs WHERE created_at < '2024-01-01'; -- Removed 90% of rows
-- Planner still thinks table is huge!
After data distribution shifts: Perhaps a new product becomes wildly popular, changing the frequency distribution of product_id values. Old statistics won’t reflect this change.
The Autovacuum Safety Net
PostgreSQL tries to keep statistics fresh automatically through the autovacuum daemon. It runs ANALYZE in the background when tables change significantly:
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
This means ANALYZE runs automatically after 50 rows plus 10% of the table changes. For a million-row table, that’s 100,050 changes.
But autovacuum is conservative. For critical, fast-changing tables, manual intervention helps:
-- After your ETL job
COPY staging_data FROM 'data.csv';
ANALYZE staging_data; -- Don't wait for autovacuum
Fine-Tuning the Detail Level
Sometimes the default statistics aren’t detailed enough. PostgreSQL samples 300 × default_statistics_target rows per column (default: 100, meaning 30,000 rows).
For columns with complex distributions:
ALTER TABLE orders ALTER COLUMN product_category SET STATISTICS 500;
ANALYZE orders;
This tells PostgreSQL to collect much more detailed histograms for that column. The tradeoff? ANALYZE takes longer and uses more memory, but complex queries become more accurate.
Monitoring Your Statistics Health
You can check when tables were last analyzed:
SELECT
schemaname,
relname,
last_analyze,
last_autoanalyze,
n_mod_since_analyze
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_mod_since_analyze DESC;
The n_mod_since_analyze column shows how many rows have changed since the last ANALYZE. High numbers indicate stale statistics.
A Real-World Rescue Story
An e-commerce platform suffered mysterious slowdowns every Monday morning. After investigation, the pattern emerged:
- Weekend batch jobs loaded millions of order records
- Statistics remained outdated until autovacuum ran (often hours later)
- Morning queries used terrible plans based on Friday’s statistics
- Performance recovered mysteriously mid-morning (when autovacuum completed)
The fix was simple:
-- Added to weekend batch script
ANALYZE orders;
ANALYZE order_items;
ANALYZE inventory;
Monday morning queries became consistently fast.
The EXPLAIN ANALYZE Feedback Loop
The most powerful debugging technique combines EXPLAIN with ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';
This shows both the planned estimates and actual execution metrics:
Seq Scan on orders (cost=0.00..18334.00 rows=100 width=120)
(actual time=0.043..245.123 rows=458923 loops=1)
See the problem? The planner estimated 100 rows but found 458,923. This massive underestimate explains why it chose a sequential scan instead of an index.
Solution: Run ANALYZE and repeat the EXPLAIN—watch the plan transform.
Best Practices for a Healthy Planner
1. Trust but verify autovacuum Check that it’s enabled and tuned appropriately for your workload.
2. ANALYZE after bulk operations Don’t let the planner fly blind after major data changes.
3. Monitor statistics age Set up alerts for tables with stale statistics in production.
4. Increase statistics targets strategically Not every column needs detailed histograms—focus on filter and join columns.
5. Use EXPLAIN routinely Make it a habit to check query plans, especially when performance changes.
6. Combine with VACUUM Often you want both: VACUUM ANALYZE cleans up dead rows and updates statistics simultaneously.
The Invisible Contract
Every query you write enters an invisible contract with PostgreSQL’s planner:
“I’ll tell you what data I need. You figure out the fastest way to get it.”
ANALYZE is how you hold up your end of that bargain. By keeping statistics fresh, you give the planner the knowledge it needs to make brilliant decisions.
The planner is only as smart as the information it has. Feed it accurate statistics, and it will reward you with lightning-fast queries. Neglect ANALYZE, and even the most powerful hardware can’t save you from poor query plans.
In the end, performance tuning isn’t just about indexes and hardware—it’s about teaching your database to understand your data. And that education begins with a simple command:
ANALYZE;
Remember: PostgreSQL won’t change your data, but with ANALYZE, it will change how it thinks about your data—and that makes all the difference.
Leave a Reply