Find, Diagnose & Fix Slow PostgreSQL Queries

Bad indexes, wrong plans, stale statistics, I/O bottlenecks, table bloat. Any of these can tank query performance, and they often look the same on the surface.

Learn how Contact us

TL;DR

→Most slow queries come down to three things: a missing index, a bad join order, or the planner choosing a plan based on wrong row estimates.

→"Always slow" and "sometimes slow" are different problems that require different fixes. Plan changes, I/O variance, and table bloat can turn a fast query slow without any code change.

→I/O is the most common root cause. EXPLAIN (ANALYZE, BUFFERS) shows you exactly which node is doing the work and how much data it's reading.

→You can guide the planner to a better plan — with better statistics, rewritten queries, or targeted index changes — without reaching for planner hints.

→Trial and error is expensive at scale. You need a tool that automatically collects EXPLAIN plans via auto_explain, surfaces bad plan patterns, and flags when plans change so you find issues before users do.

Step 01

Find the root cause of your slow query

Before running a single optimization, you must determine if you are dealing with a structural flaw or a runtime regression. Chronic slowness may point to missing indexes, while intermittent spikes often signal I/O contention or plan instability.

Always slow

Bad plan or missing index

Only sometimes

I/O variance, parameter sensitivity, or plan regression

Plan changed

Check after bulk loads, VACUUM, or statistics updates

High I/O

Buffers: shared read= in EXPLAIN output

Bad estimates

row=1 estimated, rows=50000 actual

Step 02

Benchmark with EXPLAIN (ANALYZE, BUFFERS)

Standard execution time can be misleading due to system overhead or caching. To see the ground truth of how your query interacts with physical storage, you must include BUFFERS.

Download guide

EXPLAIN (ANALYZE, BUFFERS) SELECT *

FROM product_events

WHERE organization_id = 'abc123'

ORDER BY occurred_at DESC LIMIT 100;

What to look for in the output:

Shared Read:

Actual 8kB pages fetched from disk—the primary source of latency

Shared Hit:

Pages already in the buffer cache

Disk Spill:

Look for Sort Method: external merge Disk

Method:

external merge Disk—a sign work_mem is too low.

Step 03

Identify the failure mode of your query

Most slow queries are one of six things. A single slow query can be affected by more than one. The EXPLAIN output, particularly buffer counts and row estimate accuracy, details which failure mode(s) you're dealing with.

Node TreeGridTextJSON

7└Aggregate

277.59ms

8 └Nested Loop

159.53ms

9 ├Append

5.44ms

10 │ ├Index Scanstale stats

11.76ms

11 │ ├Index Scanstale stats

11.30ms

12 │ └Index Scan

8.78ms

13    └Memoize 58k
321.25ms

14 └ƒ(x)Function Scan 51k

662.44ms

15└Subquery Scan

130.88ms

16 └Nested Loop

164.08ms

17 ├Append

5.47ms

18 │ ├Index Scanstale stats

12.21ms

19 │ ├Index Scan

12.14ms

Step 04

Fix the issue at the root

The fix order matters. Indexes and statistics are permanent fixes. Planner hints and scaling are not. They buy time while you address what's actually wrong.

Add or redesign the index

Fix statistics: Use ANALYZE or CREATE STATISTICS to improve row estimates.

Rewrite the query: Use MATERIALIZED CTEs or better join logic to guide the planner.

Tune cost settings: Adjust random_page_cost or work_mem to align with your hardware.

Use planner hints: Deploy pg_hint_plan only as a temporary bridge to force good plans.

Scale resources: Increase I/O or compute only after structural fixes are exhausted.

Step 05

Verify & watch query performance

Re-run EXPLAIN (ANALYZE, BUFFERS) after each change. A real fix reduces buffer counts and brings estimated rows closer to actual—not just execution time.

Monitor the query over time, as plan regressions after bulk loads or statistics updates are common and often silent.

Signs your fix worked:

I/O drop: shared read counts decrease significantly

Accurate stats: rows= estimate is close to actual rows

Scan type: Seq Scan transitions to Index Scan

Memory sort: Sort Method: Disk moves to Memory or disappears.

Free Webinar

How to Compare Postgres Plans and Tune Slow Queries

Watch the recorded webinar on reading EXPLAIN plans, spotting regressions, and tuning queries with confidence.

Watch now

The product

All-in on Postgres for Over a Decade

pganalyze is the trusted provider of deep Postgres observability and performance tuning for top companies around the world, including some of the Fortune 500.

Check out the tour

pganalyze Does This Across Your Entire Workload

Doing this manually works when you know which query to look at. The real challenge is finding the queries you're not watching: the ones degrading quietly until they become incidents.

Generic observability tools may show you that a query is slow, but stop short of identifying what's causing it or how to fix it. pganalyze shows you which part of the plan node is the bottleneck. Every query. All the time.

Start 14-day free trial

What you'd do manually

What pganalyze does

Run EXPLAIN on a query you already know is slow

Automatically collects EXPLAIN plans via auto_explain for every slow query — with ANALYZE and BUFFERS

Check if the plan has changed

Detects plan regressions and shows you when a query's plan structure changes

Look for queries consuming the most resources

Tracks execution time, I/O, and volume across your full workload with query-level trends

Spot stale statistics before they cause a regression

Surfaces bad row estimates and flags tables where statistics need attention

Figure out which indexes to add

Generates specific index recommendations evaluated against your full workload, not individual queries

Check for bloat manually with complex SQL statements

Monitors table and index size growth continuously and automatically captures bloat estimates

Trusted by engineering teams running Postgres at scale

“

With pganalyze's help, we improved a query that led to an instant CPU utilization drop from 400% down to 25% on the live database server. It was really cool to see such a dramatic difference.”

— Jamie Strachan, Development Team Lead, CounterPath

“

The slow query analysis and indexing recommendations significantly reduced the time we spent debugging query performance problems.”

— Ashish Yadav, Software Engineer II, Zepto

Get in touch

Stop diagnosing slow queries one at a time

Scale your Postgres expertise. pganalyze monitors every query, collects every plan, and surfaces systemic problems—indexes, statistics, regressions, I/O, and bloat—in a single place.

Start Your 14-Day Free Trial Schedule demo