Query Statistics & Execution Plans

Advanced SQL

Monitor & Optimize

Understanding how the database executes your queries is the foundation of performance optimization. EXPLAIN ANALYZE reveals the execution plan, pg_stat_statements tracks query performance over time, and system views expose locks, connections, and vacuum status.

EXPLAIN ANALYZE — See actual execution times and row counts
pg_stat_statements — Track slow queries and cache hit ratios
Index Usage — Find unused indexes and missing indexes
Lock Monitoring — Detect blocking queries and deadlocks

The difference between a 1ms query and a 10s query is usually an execution plan issue — and EXPLAIN is how you find it.

EXPLAIN ANALYZE Deep Dive

-- Full execution plan analysis
EXPLAIN (
  ANALYZE, BUFFERS, COSTS, TIMING, VERBOSE, FORMAT JSON
)
SELECT
  d.department_name,
  COUNT(e.employee_id) AS emp_count
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Key EXPLAIN Output Sections

Section	What It Shows
Cost	Estimated startup/total cost (abstract units)
Rows	Estimated vs actual row counts
Time	Actual execution time (ms)
Buffers	shared hits (cache) vs reads (disk)
Planning Time	Time to generate the plan
Execution Time	Total time including all nodes

ℹ️

Key Insight: The Buffers option shows shared hits (from cache), shared reads (from disk), and temp usage. High shared reads indicates the query is I/O bound — consider adding indexes or increasing shared_buffers.

Execution Plan Operators

pg_stat_statements

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Queries with highest I/O (low cache hit ratio)
SELECT
  query,
  shared_blks_read,
  shared_blks_hit,
  ROUND(shared_blks_hit * 100.0 /
    NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Index Usage Statistics

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Identify unused indexes (candidates for removal)
SELECT
  indexrelname, idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

Table Statistics & Bloat

-- Table bloat analysis
SELECT
  schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) AS index_size,
  n_live_tup, n_dead_tup,
  ROUND(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

⚠️

Warning: High dead tuple percentage (>10%) indicates vacuum is falling behind. Run VACUUM VERBOSE tablename; manually or tune autovacuum settings.

Lock Monitoring

-- Check current locks (blocking queries)
SELECT
  l.pid, l.mode, l.granted, a.query, a.query_start,
  NOW() - a.query_start AS duration
FROM pg_locks l
INNER JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted
ORDER BY a.query_start;

-- Check for long-running queries
SELECT pid, query, state, query_start,
  NOW() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY query_start;

Vacuum and Analyze

-- Manual vacuum with statistics update
VACUUM (ANALYZE, VERBOSE) employees;

-- Check vacuum progress
SELECT relname, phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

-- Update table statistics
ANALYZE employees;

-- Increase statistics target for important columns
ALTER TABLE employees ALTER COLUMN salary SET STATISTICS 1000;

BigQuery Execution Details

-- BigQuery job statistics
SELECT
  job_id, creation_time, end_time,
  TIMESTAMP_DIFF(end_time, creation_time, SECOND) AS duration_seconds,
  total_bytes_processed,
  ROUND(total_bytes_processed / POWER(1024, 3), 2) AS processed_gb,
  cache_hit
FROM `project.dataset.INFORMATION_SCHEMA.JOBS`
WHERE query LIKE '%SELECT%'
ORDER BY creation_time DESC LIMIT 10;

Quiz: Test Your Knowledge

Follow-Up Questions

How do you interpret a query plan's cost estimates?
What's the difference between a bitmap scan and an index scan?
How do you identify queries that need optimization?
Explain the impact of table statistics on query planning.
How do you monitor query performance in real-time?

Key Takeaways

EXPLAIN ANALYZE — Always use BUFFERS to see I/O; compare estimated vs actual rows
pg_stat_statements — Track slow queries, cache hit ratios, and I/O patterns
Unused indexes — Remove indexes with 0 scans to improve write performance
Dead tuples — Monitor n_dead_tup; >10% means vacuum is behind
Lock monitoring — pg_locks + pg_stat_activity to detect blocking queries
ANALYZE — Update statistics so the optimizer picks the right plan

Query Statistics & Execution Plans — EXPLAIN, pg_stat_statements, Performance Monitoring