EXPLAIN ANALYZE and Index Optimization

Intermediate15 minTrending

Diagnose slow queries with EXPLAIN ANALYZE and create targeted indexes to dramatically improve PostgreSQL query performance.

Prerequisites

-PostgreSQL database with tables and data
-psql or database client

Steps

Analyze a query execution plan

Run EXPLAIN ANALYZE to see the actual execution plan with real timings.

$ psql -U postgres -d mydb -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE customer_id = 1001 AND status = 'pending';"

BUFFERS shows I/O statistics. Look for Seq Scan on large tables as the primary indicator of missing indexes.

Find missing indexes with pg_stat_user_tables

Identify tables with high sequential scan counts that could benefit from indexes.

$ psql -U postgres -d mydb -c "SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup FROM pg_stat_user_tables WHERE seq_scan > 100 ORDER BY seq_tup_read DESC LIMIT 10;"

Create an index for a common query pattern

Add a composite index to support filtered queries efficiently.

$ psql -U postgres -d mydb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status);"

CONCURRENTLY builds the index without locking the table for writes. Always use it in production.

CREATE INDEX CONCURRENTLY cannot run inside a transaction block.

Check index usage statistics

Verify that your indexes are actually being used by queries.

$ psql -U postgres -d mydb -c "SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE schemaname = 'public' ORDER BY idx_scan DESC LIMIT 10;"

Find unused indexes wasting space

Identify indexes that consume disk space but are never used by the query planner.

$ psql -U postgres -d mydb -c "SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%' ORDER BY pg_relation_size(indexrelid) DESC;"

Full Script

full-script.sh

#!/bin/bash
# PostgreSQL Performance Tuning
set -euo pipefail

DB="mydb"
USER="postgres"

echo "=== Tables with high sequential scans ==="
psql -U "$USER" -d "$DB" -c "
SELECT relname AS table,
       seq_scan AS seq_scans,
       seq_tup_read AS rows_seq_read,
       idx_scan AS idx_scans,
       n_live_tup AS live_rows
FROM pg_stat_user_tables
WHERE seq_scan > 50
ORDER BY seq_tup_read DESC
LIMIT 10;
"

echo ""
echo "=== Unused indexes ==="
psql -U "$USER" -d "$DB" -c "
SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 10;
"

echo ""
echo "=== Most used indexes ==="
psql -U "$USER" -d "$DB" -c "
SELECT indexrelname AS index_name,
       idx_scan AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 10;
"

echo ""
echo "=== Cache hit ratio ==="
psql -U "$USER" -d "$DB" -c "
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = '$DB';
"

FAQ

Discussion

Loading comments...

#!/bin/bash # PostgreSQL Performance Tuning set -euo pipefail DB="mydb" USER="postgres" echo "=== Tables with high sequential scans ===" psql -U "$USER" -d "$DB" -c " SELECT relname AS table, seq_scan AS seq_scans, seq_tup_read AS rows_seq_read, idx_scan AS idx_scans, n_live_tup AS live_rows FROM pg_stat_user_tables WHERE seq_scan > 50 ORDER BY seq_tup_read DESC LIMIT 10; " echo "" echo "=== Unused indexes ===" psql -U "$USER" -d "$DB" -c " SELECT indexrelname AS index_name, pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan AS times_used FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%' ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10; " echo "" echo "=== Most used indexes ===" psql -U "$USER" -d "$DB" -c " SELECT indexrelname AS index_name, idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 10; " echo "" echo "=== Cache hit ratio ===" psql -U "$USER" -d "$DB" -c " SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_ratio FROM pg_stat_database WHERE datname = '$DB'; "