PostgreSQL slow queries — 7 ways to find and fix performance bottlenecks

Every PostgreSQL database eventually develops slow queries. It might start small: a dashboard that takes a bit longer to load, an API endpoint that times out during peak traffic, a report that used to run in seconds and now takes minutes. The tricky part is that slow queries rarely announce themselves. They creep in as data grows, schemas change and new features pile on.

This article covers seven practical ways to find the queries that are hurting your database and fix them. Not theoretical advice, but actual tools and techniques you can apply to a running PostgreSQL instance today.

1. Enable pg_stat_statements to find your worst offenders

The single most useful extension for tracking slow queries in PostgreSQL is pg_stat_statements. It records execution statistics for every query that runs against your database, including how many times it ran, total execution time, rows returned and more.

Most performance problems come from a handful of queries. pg_stat_statements lets you find them without guessing.

To enable it, add the extension to your postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

After restarting PostgreSQL, create the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Then query it to find the most time-consuming queries:

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS total_time_ms,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percent_total,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This shows you which queries consume the most cumulative time. A query that runs 50,000 times a day at 100ms each is a bigger problem than a query that runs once at 5 seconds. The percent_total column makes this obvious.

You can also find queries with the highest average execution time:

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round(max_exec_time::numeric, 2) AS max_time_ms,
    rows,
    query
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 20;

The WHERE calls > 10 filter avoids one-off admin queries that would distort the results.

Reset statistics periodically to keep the data relevant:

SELECT pg_stat_statements_reset();

pg_stat_statements is the starting point. Everything else in this article builds on knowing which queries to focus on.

2. Use EXPLAIN ANALYZE to understand what's actually happening

Once you know which queries are slow, EXPLAIN ANALYZE tells you why. It runs the query and shows the execution plan PostgreSQL actually used, including the time spent at each step.

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
  AND o.status = 'completed';

The output looks something like this:

Hash Join  (cost=12.50..345.00 rows=150 actual time=0.82..15.43 rows=143 loops=1)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders o  (cost=0.00..310.00 rows=150 actual time=0.02..14.20 rows=143 loops=1)
        Filter: ((created_at > '2026-01-01') AND (status = 'completed'))
        Rows Removed by Filter: 99857
  ->  Hash  (cost=10.00..10.00 rows=200 actual time=0.45..0.45 rows=200 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..10.00 rows=200 actual time=0.01..0.20 rows=200 loops=1)
Planning Time: 0.15 ms
Execution Time: 15.60 ms

The important things to look for:

The Seq Scan on orders above is the bottleneck. It reads 100,000 rows to return 143. An index on (status, created_at) would fix this:

CREATE INDEX idx_orders_status_created ON orders (status, created_at);

After creating the index, run EXPLAIN ANALYZE again. You should see an Index Scan or Bitmap Index Scan replacing the sequential scan, and the execution time dropping significantly.

One thing people miss: EXPLAIN without ANALYZE shows the plan but doesn't execute the query. It gives you estimates, not actual numbers. Always use ANALYZE when debugging performance, unless the query modifies data (in that case, wrap it in a transaction and roll back).

3. Configure the slow query log

pg_stat_statements gives you aggregate data, but sometimes you need to see individual slow queries as they happen. PostgreSQL's built-in slow query log captures every query that exceeds a time threshold.

Add these settings to postgresql.conf:

log_min_duration_statement = 500
log_statement = 'none'
log_duration = off
log_line_prefix = '%t [%p] %u@%d '

This logs any query that takes longer than 500 milliseconds. The log_line_prefix adds the timestamp, process ID, username and database name to each log entry, which is essential for debugging.

Setting log_min_duration_statement = 0 logs every query. This is useful for short debugging sessions but generates enormous log files on busy databases. For production, start with 500ms or 1000ms and lower it as you fix the worst offenders.

The log entries look like this:

2026-02-10 14:23:45 UTC [12345] app_user@mydb LOG: duration: 2345.678 ms  statement: 
    SELECT u.*, p.* FROM users u JOIN purchases p ON p.user_id = u.id 
    WHERE u.region = 'eu' ORDER BY p.created_at DESC;

For more structured analysis, tools like pgBadger can parse these logs and generate reports showing the slowest queries, most frequent queries and query patterns over time. But the raw log is often enough to spot problems.

A practical approach: enable the slow query log in production at 1000ms, review it weekly, fix the top offenders, then lower the threshold to 500ms. Repeat until the log is mostly quiet.

4. Fix missing and misused indexes

Missing indexes are the most common cause of slow queries in PostgreSQL. But "add more indexes" isn't always the answer. Sometimes existing indexes aren't being used, or the wrong type of index was created.

Finding missing indexes. Start with the query from pg_stat_statements, then check if the tables involved have appropriate indexes:

SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 15;

Tables with a high seq_tup_read and low idx_scan are being scanned sequentially when they probably shouldn't be. A table with 10 million rows and zero index scans is almost certainly missing an index.

Finding unused indexes. Indexes you never use still cost write performance:

SELECT
    indexrelname AS index_name,
    relname AS table_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (
      SELECT indexrelid FROM pg_index WHERE indisprimary
  )
ORDER BY pg_relation_size(indexrelid) DESC;

This shows indexes that have never been scanned (excluding primary keys). If an index is 500 MB and has zero scans, it's slowing down every write for nothing. Drop it.

Common indexing mistakes:

• Indexing a column with very low cardinality (like a boolean is_active column with 99% true values). The planner often prefers a sequential scan because the index doesn't filter out enough rows.
• Creating single-column indexes when your queries filter on multiple columns. A composite index on (status, created_at) is much better than separate indexes on status and created_at when your WHERE clause uses both.
• Forgetting partial indexes. If 95% of queries filter for active records, create a partial index:

CREATE INDEX idx_orders_active ON orders (customer_id, created_at)
WHERE status = 'active';

This index is smaller, faster to scan and faster to maintain than a full index.

5. Tune PostgreSQL memory and planner settings

Default PostgreSQL configuration is deliberately conservative. It assumes the server has 128 MB of RAM and a single spinning disk. If you're running on a modern server with 16 GB of RAM and SSDs, the defaults are leaving performance on the table.

The key settings that affect query performance:

shared_buffers is the most important one. PostgreSQL uses this as its primary data cache. Too low and it constantly re-reads data from disk. Too high and it competes with the OS page cache. 25% of total RAM is a good starting point for most workloads.

work_mem is tricky because it's per-operation, not per-query. A complex query with five sort operations and three hash joins could allocate up to 8x work_mem. Setting it to 256 MB sounds reasonable until 50 concurrent connections each allocate multiple chunks. Start with 64 MB and monitor.

random_page_cost is the one that catches most people. The default of 4.0 tells the planner that random disk reads are four times more expensive than sequential reads. That was true for spinning disks. On SSDs, random and sequential reads are nearly identical. Lowering this to 1.1 makes the planner much more willing to use indexes, which is usually what you want on SSD storage.

You can change these without a restart (except shared_buffers) using:

ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET random_page_cost = 1.1;
ALTER SYSTEM SET effective_cache_size = '12GB';
SELECT pg_reload_conf();

After changing settings, test with EXPLAIN ANALYZE on your slow queries. You should see different plan choices, especially more index scans and in-memory sorts.

6. Rewrite problematic query patterns

Sometimes the query itself is the problem. No amount of indexing or tuning will fix a fundamentally inefficient query. Here are patterns that consistently cause performance issues and how to fix them.

SELECT * when you only need a few columns. This forces PostgreSQL to read and transfer every column, including large text or JSONB fields:

-- Slow: reads everything, including a 10 KB description column
SELECT * FROM products WHERE category = 'electronics';

-- Better: only fetches what you need
SELECT id, name, price FROM products WHERE category = 'electronics';

This matters more than people think, especially with TOAST (The Oversized Attribute Storage Technique). Large columns are stored separately, and fetching them requires additional disk reads.

Correlated subqueries that run once per row. The planner sometimes can't flatten these:

-- Slow: subquery executes for each order row
SELECT o.id, o.total,
    (SELECT name FROM customers c WHERE c.id = o.customer_id)
FROM orders o
WHERE o.created_at > '2026-01-01';

-- Better: explicit JOIN
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01';

Using OFFSET for pagination on large datasets. OFFSET 100000 means PostgreSQL fetches and discards 100,000 rows before returning results:

-- Slow: scans and discards 100,000 rows
SELECT * FROM events ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- Better: keyset pagination using the last seen value
SELECT * FROM events
WHERE created_at < '2026-01-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;

Keyset pagination is consistently fast regardless of how deep into the result set you go. It requires an index on the column you're paginating by.

Unnecessary DISTINCT or GROUP BY. If you're adding DISTINCT because a JOIN produces duplicates, the JOIN is probably wrong. Fix the JOIN condition instead of papering over it with DISTINCT.

Functions in WHERE clauses that prevent index usage:

-- Index on created_at won't be used
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2026;

-- Rewrite to use the index
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

7. Keep statistics up to date with ANALYZE and VACUUM

PostgreSQL's query planner relies on table statistics to make decisions. How many rows does a table have? What's the distribution of values in each column? How many distinct values are there? If these statistics are wrong, the planner makes bad choices.

ANALYZE collects fresh statistics about table contents. VACUUM reclaims space from deleted or updated rows (dead tuples) that PostgreSQL can't reuse. Both are essential for sustained query performance.

Autovacuum handles this automatically by default, but it doesn't always keep up. Large batch operations, bulk deletes and rapidly growing tables can outpace the default autovacuum settings.

Check if your statistics are stale:

SELECT
    relname AS table_name,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

Tables with a high percentage of dead tuples need vacuuming. Tables that haven't been analyzed recently may have stale statistics.

If a table had 10,000 rows when statistics were collected but now has 10 million, the planner might choose a sequential scan based on the old row count when an index scan would be far more efficient. Running ANALYZE fixes this:

ANALYZE orders;

For autovacuum tuning, the defaults are cautious. On busy databases, consider adjusting:

autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_vacuum_cost_delay = 2ms

The scale factors control when autovacuum kicks in. The default vacuum_scale_factor of 0.2 means autovacuum runs after 20% of rows have been modified. On a 100 million row table, that's 20 million dead tuples before cleanup starts. Lowering it to 0.05 (5%) keeps things cleaner.

For large tables with specific requirements, you can set per-table autovacuum settings:

ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

Keeping your data safe while you optimize

Tuning queries and tweaking PostgreSQL configuration is relatively safe work. But mistakes happen. A dropped index on a production table during peak hours, a configuration change that causes out-of-memory crashes, an ANALYZE on a massive table that locks things at the wrong moment.

Having reliable backups means you can optimize with confidence. PostgreSQL backup tools like Databasus handle automated scheduled backups with compression, encryption and multiple storage destinations. It's an industry standard for PostgreSQL backup tools, suitable for individual developers and enterprise teams.

Putting it all together

Fixing slow queries in PostgreSQL isn't a one-time task. It's a cycle: identify the slow queries with pg_stat_statements, understand why they're slow with EXPLAIN ANALYZE, fix the root cause (missing index, bad query pattern, stale statistics or wrong configuration) and then monitor to make sure the fix holds.

Start with pg_stat_statements if you haven't already. It takes five minutes to set up and immediately shows you where your database is spending its time. From there, work through the list: check your indexes, review your configuration settings, look for problematic query patterns and make sure autovacuum is keeping up.

Most PostgreSQL performance problems have straightforward solutions. The hard part is knowing where to look.