How to Find and Fix a PostgreSQL Slow Query

When a PostgreSQL slow query shows up, the instinct is to open the one query someone complained about and start adding indexes. I do it the other way round. First I measure which queries cost the database the most time in total, then I read their execution plans, and only then do I change anything. Most of the time the fix is an index that matches how the query filters and sorts, an ORM generating hundreds of small queries, or statistics and vacuum that have fallen behind the data. Here is the process step by step, with the commands I actually run.

Step 1: Enable pg_stat_statements

pg_stat_statements ships with PostgreSQL as a contrib extension. It records every normalized query with its call count and timing. It has to be preloaded, so it needs a restart the first time:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
track_io_timing = on

-- after restart, in the database you care about
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On Amazon RDS and Aurora it is set through the parameter group; on most managed services it is already loaded and you only need the CREATE EXTENSION. The overhead is small, and I leave it on permanently.

I also turn on the slow query log as a second source, so individual bad executions get captured with their real parameters:

log_min_duration_statement = 500   # ms; log anything slower

Step 2: Rank every PostgreSQL slow query by total time

The slowest single query is often not the problem. A 3 ms query called two million times an hour costs far more than a 4-second report run twice a day. Sort by total time:

SELECT round(total_exec_time::numeric / 1000, 1) AS total_s,
       calls,
       round(mean_exec_time::numeric, 2)          AS mean_ms,
       rows / NULLIF(calls, 0)                    AS rows_per_call,
       left(query, 120)                           AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

The column names are total_exec_time and mean_exec_time from PostgreSQL 13 onward; older versions use total_time and mean_time. Reset the counters with SELECT pg_stat_statements_reset(); after a deploy if you want a clean window. The top five rows are usually most of the load, and that is the list I work from.

Step 3: Read EXPLAIN (ANALYZE, BUFFERS)

Take a query from the list, fill in realistic parameters from the slow log, and run it with EXPLAIN (ANALYZE, BUFFERS). ANALYZE actually executes the query, so wrap writes in a transaction you roll back.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 4821 AND status = 'open'
ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=812.4..812.5 rows=20 loops=1)
  Buffers: shared hit=1204 read=40211
  ->  Sort  (actual time=812.4..812.4 rows=20 loops=1)
        Sort Method: top-N heapsort  Memory: 30kB
        ->  Seq Scan on orders  (actual time=0.03..801.9 rows=1840 loops=1)
              Filter: ((customer_id = 4821) AND (status = 'open'))
              Rows Removed by Filter: 3998160

What I look for, in order:

• Where the actual time accumulates. Times are cumulative up the tree, so find the deepest node with a large actual time.
• Rows Removed by Filter. Reading four million rows to return 1,840 means there is no usable index for this filter.
• Estimated versus actual rows. If the planner expected 10 rows and got 200,000, its statistics are wrong and it will pick bad join strategies.
• Buffers read versus hit. A large read count means data came from disk or the OS cache rather than shared buffers.
• Sorts and hashes spilling to disk. Sort Method: external merge Disk: ... means work_mem (default 4MB) was too small for that operation.
• Nested Loop with a large loops count. An inner index scan executed 50,000 times is often where a join goes wrong.

Step 4: Choose the right index type

Most postgres query optimization comes down to an index that matches the query shape. Build them with CONCURRENTLY on a live table so writes are not blocked.

Composite B-tree

The default, and the right answer most of the time. Put equality columns first and the sort column last, so the index can both filter and return rows already ordered:

CREATE INDEX CONCURRENTLY idx_orders_cust_status_created
  ON orders (customer_id, status, created_at DESC);

With this index the plan above becomes an Index Scan that reads about 20 rows and stops.

Partial index

If most rows are status = 'closed' and queries only ever ask for open ones, index just those. It is smaller, faster and cheaper to maintain:

CREATE INDEX CONCURRENTLY idx_orders_open
  ON orders (customer_id, created_at DESC) WHERE status = 'open';

GIN for JSONB, arrays and text search

A B-tree cannot help with data @> '{"plan":"pro"}' or ILIKE '%smith%'. Use GIN, with the pg_trgm extension for substring search:

CREATE INDEX CONCURRENTLY idx_events_data ON events USING gin (data jsonb_path_ops);
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_customers_name_trgm ON customers USING gin (name gin_trgm_ops);

For very large, append-only time-series tables, a BRIN index on the timestamp is tiny and often enough. And check pg_stat_user_indexes for indexes with idx_scan = 0: every unused index slows down every write.

Step 5: Fix N+1 queries from the ORM

A common pattern in pg_stat_statements is a trivial query like SELECT ... FROM order_items WHERE order_id = $1 at the top by calls, with a mean time under a millisecond. Each call is fast; the problem is the application issuing one per row in a loop. Django, Rails, SQLAlchemy, Prisma, TypeORM and Eloquent all do this by default when you touch a relation inside a loop.

The fix is in the application, not the database: select_related/prefetch_related in Django, selectinload in SQLAlchemy, include in Prisma, with() in Laravel. A page that made 300 queries typically makes two or three afterwards. This is also the most common cause of slow GraphQL APIs, which I cover in GraphQL vs REST.

Step 6: Refresh stale statistics with ANALYZE

The planner chooses plans from statistics that autovacuum refreshes after a share of the table changes. After a bulk load, a big delete or a data migration, those statistics can be badly out of date, and a query that was fast yesterday suddenly picks a sequential scan. The symptom is estimated rows wildly different from actual rows in the plan. The fix is quick:

ANALYZE orders;
-- for skewed columns, collect more detail
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

For columns that are correlated, such as city and postcode, extended statistics (CREATE STATISTICS ... (dependencies)) stop the planner multiplying selectivities as if they were independent.

Step 7: Check bloat and autovacuum

Because of MVCC, every UPDATE and DELETE leaves a dead row version behind until vacuum reclaims it. With default settings, autovacuum only processes a table once about 20% of it has changed (autovacuum_vacuum_scale_factor = 0.2). On a 200-million-row table that is 40 million dead rows before it even starts, and queries read far more pages than the live data needs.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- tune the busy tables individually
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
                        autovacuum_analyze_scale_factor = 0.01);

Also look for long-running or idle in transaction sessions in pg_stat_activity. A single forgotten open transaction stops vacuum from removing any row versions newer than it. Reclaiming space that is already bloated needs VACUUM FULL, which locks the table, or pg_repack, which does not.

Step 8: Add connection pooling

Sometimes no individual query is slow, but everything is slow at peak. Each PostgreSQL connection is a separate backend process with its own memory, and hundreds of mostly idle connections from app servers or serverless functions compete for CPU and RAM. The error at the limit is FATAL: sorry, too many clients already. PgBouncer in transaction mode lets thousands of client connections share a small pool of real ones:

[pgbouncer]
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 30

Transaction mode breaks session features such as SET without LOCAL, session-level advisory locks and, on older PgBouncer versions, server-side prepared statements, so test the application against it before rolling out.

When it is worth getting help

If you have pg_stat_statements output and a plan you cannot make sense of, or the obvious index did not help, that is the point where a second pair of eyes saves days. I do this as part of my PostgreSQL consulting service: read-only access first, a written list of what is costing time and why, then fixes with before-and-after plans. Small investigations are billed at $20/hour; larger tuning work gets a fixed price agreed in writing. If the root cause is in application code, I can fix that side too through Python and FastAPI development or Node.js development. Either way, send me the slow query and its plan.

Frequently asked questions