Intermediate LevelPostgreSQL
How do you analyze query performance using the pg_stat_statements extension?
❓ Interview Prompt: Explain `pg_stat_statements` extension, query normalization, tracking calls, total_exec_time, mean_exec_time, and shared buffer hit ratios.
'pg_stat_statements' is the essential extension for analyzing query performance in production PostgreSQL databases:
Key Performance Indicators:
•
'calls': Total execution count.
•
'mean_exec_time': Average execution latency (ms).
•
'shared_blks_hit' vs 'shared_blks_read': Buffer cache hit ratio (high 'shared_blks_read' indicates physical disk reads!).
Code Implementation Example
sql
-- Top 5 queries by total execution time
SELECT query, calls, round(total_exec_time::numeric, 2) AS total_time_ms,
round(mean_exec_time::numeric, 2) AS mean_time_ms,
round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;💡Identifying slow queries and low buffer hit ratios via pg_stat_statements.
🎯 Interview Key Takeaway
Always enable pg_stat_statements in shared_preload_libraries to identify slow production queries.
#pg_stat_statements#Query Analysis#Performance#Intermediate
Back to all PostgreSQL Questions→