Fix: PostgreSQL Slow Query — Finding and Optimizing with EXPLAIN ANALYZE

The Problem

A PostgreSQL query runs much slower than expected:

SELECT * FROM orders WHERE customer_id = 42;
-- Takes 8 seconds on a table with 10 million rows

Or an application endpoint is slow and tracing shows the database query is the bottleneck:

GET /api/orders/customer/42  — 8234ms
  DB: SELECT * FROM orders WHERE customer_id = 42  — 7891ms

Or a query was fast in development but is slow in production with real data volumes.

Step 1: Run EXPLAIN ANALYZE

EXPLAIN ANALYZE is the starting point for every slow query investigation. It shows the query plan and actual execution times:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Sample output:

Seq Scan on orders  (cost=0.00..250000.00 rows=150 width=128) (actual time=0.042..7823.443 rows=150 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 9999850
Planning Time: 0.124 ms
Execution Time: 7823.512 ms

Key things to look for:

The Seq Scan with 9,999,850 rows removed means PostgreSQL is reading the entire 10M row table to find 150 matching rows. An index would fix this.

Fix 1: Add a Missing Index

For equality lookups, range queries, and foreign key columns, an index dramatically reduces scan cost:

-- The slow query
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Seq Scan on orders... Execution Time: 7823.512 ms

-- Add an index on the filter column
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

-- Now run the same query
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Index Scan using idx_orders_customer_id on orders...  Execution Time: 0.234 ms

CONCURRENTLY creates the index without locking the table — safe for production. Without it, the table is locked for writes during index creation.

Composite indexes — when queries filter on multiple columns:

-- Query filtering on status AND created_at
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

-- Single-column indexes help but a composite index is more efficient
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC);

-- Column order matters: put the equality column first, range column second

Partial indexes — for queries that filter on a specific value:

-- If most queries only look at 'pending' orders
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (customer_id, created_at)
WHERE status = 'pending';
-- Much smaller index — only indexes rows where status = 'pending'

Don’t add indexes blindly. Each index slows down INSERT, UPDATE, and DELETE operations and consumes disk space. Add indexes for columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses of slow queries.

Fix 2: Read EXPLAIN Output for Joins

Slow joins are another common cause of slow queries. Look for Nested Loop on large tables:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Nested Loop  (cost=...) (actual time=0.124..45231.223 rows=50000 loops=1)
  ->  Seq Scan on orders o  (actual time=0.021..5231.234 rows=50000 loops=1)
        Filter: (status = 'pending')
  ->  Index Scan using customers_pkey on customers c  (actual time=0.001..0.001 rows=1 loops=50000)
        Index Cond: (id = o.customer_id)

The nested loop runs 50,000 times — once per pending order. Each lookup is fast (index scan), but 50,000 lookups add up.

Fix — ensure the join column is indexed (foreign key):

-- If orders.customer_id doesn't have an index, every inner loop is a seq scan
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

Fix — add index on the filter column:

CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- Now PostgreSQL can use an index scan instead of seq scan for the outer loop

Fix 3: Fix N+1 Query Problems

N+1 queries — making one query to get N records, then N more queries to get related data — are invisible to PostgreSQL but cause massive slowness in applications:

# N+1 in Python/SQLAlchemy — looks innocent, runs 1001 queries
customers = session.query(Customer).limit(100).all()
for customer in customers:
    orders = session.query(Order).filter_by(customer_id=customer.id).all()
    # 1 query for customers + 100 queries for orders = 101 queries total

Fix — use a JOIN to fetch all data in one query:

# SQLAlchemy — eager load with joinedload
from sqlalchemy.orm import joinedload

customers = (
    session.query(Customer)
    .options(joinedload(Customer.orders))
    .limit(100)
    .all()
)
# 1 query total — customers and orders fetched together

-- Direct SQL equivalent
SELECT c.*, o.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LIMIT 100;

In TypeORM:

// N+1 — DON'T do this
const customers = await customerRepo.find({ take: 100 });
for (const customer of customers) {
  customer.orders = await orderRepo.find({ where: { customerId: customer.id } });
}

// Correct — eager load with relations
const customers = await customerRepo.find({
  take: 100,
  relations: { orders: true },  // JOIN in one query
});

Fix 4: Use pg_stat_statements to Find Slow Queries

Instead of profiling individual queries, use pg_stat_statements to find the worst-performing queries across your entire application:

-- Enable the extension (requires superuser, needs postgresql.conf change + restart)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- In postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

-- Find the slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This shows you which queries collectively consume the most time — the best targets for optimization. A query that runs 10ms but is called 100,000 times/hour (1000 total seconds) is a better optimization target than a 5-second query called once a day.

Reset statistics after optimizing:

SELECT pg_stat_statements_reset();

Fix 5: Optimize Queries with LIMIT and Pagination

Fetching all matching rows when you only need the first page is wasteful:

-- SLOW — fetches and sorts all 1M rows to return 20
SELECT * FROM posts ORDER BY created_at DESC;

-- FAST — fetches only 20 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;

-- Keyset pagination (faster than OFFSET for large pages)
-- Get next page after the last seen id
SELECT * FROM posts
WHERE created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

Avoid OFFSET for large page numbers:

-- SLOW — must scan and discard 100,000 rows to reach page 5001
SELECT * FROM posts ORDER BY id OFFSET 100000 LIMIT 20;

-- FAST — use keyset pagination instead
SELECT * FROM posts WHERE id < $last_seen_id ORDER BY id DESC LIMIT 20;

Fix 6: Optimize with Covering Indexes (Index Only Scans)

An Index Only Scan — where all needed columns are in the index — is the fastest possible read:

-- Query only needs id, status, created_at
SELECT id, status, created_at FROM orders WHERE customer_id = 42;

-- Regular index — still needs to fetch table rows for status and created_at
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index — includes all needed columns
CREATE INDEX idx_orders_customer_covering
ON orders (customer_id)
INCLUDE (status, created_at);
-- Now: Index Only Scan — no table access needed

EXPLAIN ANALYZE SELECT id, status, created_at FROM orders WHERE customer_id = 42;
-- Index Only Scan using idx_orders_customer_covering on orders...
-- Heap Fetches: 0   ← No table rows accessed at all

Fix 7: Check for Missing Statistics and Vacuum

If PostgreSQL’s query planner makes bad decisions (choosing a seq scan when an index would be faster), it may have stale statistics:

-- Check table statistics age
SELECT schemaname, tablename, last_vacuum, last_analyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manually analyze (update statistics)
ANALYZE orders;

-- Vacuum to remove dead tuples and reclaim space
VACUUM ANALYZE orders;

If a table has many dead tuples (from frequent updates/deletes), seq scans slow down because PostgreSQL reads dead rows too. VACUUM removes dead tuples.

Set autovacuum more aggressively for high-churn tables:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- Vacuum when 1% of rows are dead (default 20%)
  autovacuum_analyze_scale_factor = 0.005  -- Analyze when 0.5% of rows change
);

Still Not Working?

Force an index to test if PostgreSQL is ignoring it:

-- Temporarily disable seq scan to force index use
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
SET enable_seqscan = ON;

-- If the query is faster with the index forced, PostgreSQL's statistics are wrong
-- Run ANALYZE orders; to fix the statistics

Check if the index is actually being used:

SELECT indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'orders';
-- idx_scan = 0 means the index has never been used

Identify table bloat — a table with many dead rows acts as if it’s larger than it is:

SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS total_size,
  pg_size_pretty(pg_relation_size(tablename::regclass)) AS table_size,
  n_dead_tup AS dead_rows
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

For related database issues, see Fix: PostgreSQL Deadlock Detected and Fix: MySQL Lock Wait Timeout Exceeded.