PostgreSQL

Query Analysis Commands

Analyze and optimize PostgreSQL queries using EXPLAIN, statistics views, and performance monitoring.

6 commands

Pro Tips

Use EXPLAIN ANALYZE to see actual execution times, not just estimates.

Enable \timing in psql to see query execution time.

Common Mistakes

EXPLAIN ANALYZE actually executes the query - use BEGIN/ROLLBACK for writes.

Commands

Explain query

$ EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

Analyze query execution plan.

Long running queries

$ SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

Find slow running queries.

Index usage

$ SELECT indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

View index usage statistics.

Table stats

$ SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

View table statistics.

Lock info

$ SELECT * FROM pg_locks WHERE NOT granted;

View blocked locks.

Timing on

$ \timing on

Enable query timing display.