PostgreSQL Query Performance Insights
summary
Compares concrete tools and outcomes: uses pg_stat_activity for live connections, pg_stat_statements for accumulated stats, auto_explain and pgBadger for logged plans, and Percona’s pg_stat_monitor (v1.0) for time-bucketed history. Shows a real bug where JDBC typecasting causes a sequential scan that runs 3.5 hours versus milliseconds with an index. Documents pg_stat_monitor’s default 10 one-minute buckets, preservation of actual parameter values and plans, per-query histograms, and availability for PostgreSQL 12+ so users can reproduce which tool actually finds the cause.
AI-written summary · about
chapters (14)
- 0:00PostgreSQL query performance
- 2:51Query performance analysis
- 5:39Query processing stages
- 7:56EXPLAIN and EXPLAIN ANALYZE
- 10:53Cost parameters explained
- 13:41Complex query optimization
- 16:27Query plan structure
- 18:37PostgreSQL performance tools
- 20:25Connections with pg_stat_activity
- 25:25Query stats with pg_stat_statements
- 35:45auto_explain for query logging
- 39:17pgBadger log analysis
- 42:09pg_stat_monitor intro
- 49:24Summary and tool guide
related talks
50mPostgreSQL Query Performance Insights
25mPoint-in-time query tuning and observability with pg_stat_statements
57mQuery Logging and Workload Analysis
39mDeep dive into PostgreSQL internal statistics
47mIdentifying Slow Queries and Fixing Them!
27mPostgres Performance in 15 Minutes
26mTroubleshooting High CPU Utilization in PostgreSQL Databases
31mPostgres Performance for Humans