pg_stat_advisor: Optimizing PostgreSQL Planner with Insights
summary
An extension called pg_stat_advisor watches query estimates (via EXPLAIN ANALYZE), records mismatches into a memory ring buffer, and a background worker every ~10s auto-creates extended statistics when estimated vs actual row counts exceed a configurable 100x threshold (example: 10 million-row table where extended stats bring estimates to reality). It requires ANALYZE first and shared_preload_libraries restart, skips partitions/temp tables/joins, and can bloat ANALYZE so pruning useless stats is a planned improvement.
AI-written summary · about
chapters (16)
- 0:00Introduction and speaker intro
- 1:10Talk overview and goals
- 2:36Planner statistics issues and examples
- 7:40Extension pg_stat_advisor features
- 8:23How pg_stat_advisor works internally
- 12:03Configuration and parameters
- 13:26Example with analyze command
- 14:34Limitations and restrictions
- 17:51Future plans for the extension
- 21:05Q&A session begins
- 21:27PostgreSQL statistics size comparison
- 22:50Join node statistics analysis
- 25:24Statistics collection criteria
- 26:49Explain vs select command data
- 28:14Extension installation and restart
- 30:03Final Q&A and conclusion
related talks
36mAdvice is seldom welcome but efficacious
26mBe Good Friends with Query Planner
04mEnhancing Planner Estimates with Extended Statistics in PostgreSQL
50mPostgreSQL Query Performance Insights
52mUnderstanding PostgreSQL Query Plans (aka EXPLAIN)
26mpg_stats: How Postgres Internal Stats Work
23mBest Practices for Tuning Slow Postgres Queries
47mIdentifying Slow Queries and Fixing Them!