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.
TL;DR
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.
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 guideWhat to look for in the output:
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.
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.
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 trialauto_explain for every slow query — with ANALYZE and BUFFERS
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.”
The slow query analysis and indexing recommendations significantly reduced the time we spent debugging query performance problems.”
Frequently asked questions
What is pganalyze?
pganalyze is a Postgres performance optimization product for engineering teams. It captures query performance, wait event data and other critical information, finds the root cause of slow queries, bad indexes, and table bloat in minutes, and lets you investigate safely with AI tools such as Claude Code through the pganalyze MCP Server. With pganalyze Workbooks, fixes to slow queries can be tested and validated before you ship. More than 700 teams, including Notion, Auto Trader UK, and Fivetran, use it through the cloud service or self-host with pganalyze Enterprise Server.
What is the performance overhead of pganalyze on my database?
Minimal, with the recommended configuration. The pganalyze collector runs outside your database and reads Postgres statistics views on a schedule: connection activity every 10 seconds, query statistics every minute, and schema and system statistics every 10 minutes. EXPLAIN plan collection uses the Postgres auto_explain module with the settings pganalyze recommends: log only statements that run longer than 1000 ms, and keep per-node timing off, since repeatedly reading the system clock is what slows queries down.
Does pganalyze require pg_stat_statements, and does enabling it need a restart?
Yes. pganalyze reads query statistics from pg_stat_statements, which ships with Postgres. It must be listed in shared_preload_libraries, and changing that setting needs one database restart. Managed providers expose it as a parameter, and many providers such as Amazon RDS/Aurora and Supabase enable it by default.
How does the pganalyze Index Advisor decide which indexes to recommend?
The pganalyze Index Advisor looks at every query on a table, not one query at a time, and models how Postgres would use each candidate index with planner logic and a constraint programming solver. It weighs the read time an index saves against the write overhead it adds, so it can avoid an index that speeds up one query but slows down the table writes significantly. Recommendations appear within 48 hours and include the reasoning; nothing is created automatically.
How does pganalyze collect EXPLAIN plans?
Two ways. The recommended one is auto_explain: Postgres logs the plan of every query slower than a threshold you set, and the pganalyze collector reads those plans from the log through Log Insights, with parameter values normalized when PII filtering is enabled. The other is log-based EXPLAIN, where the collector runs EXPLAIN without ANALYZE for slow queries it sees in the logs. EXPLAIN plan collection is available on the Scale plan and higher.
What does the pganalyze Query Advisor do?
The pganalyze Query Advisor analyzes the EXPLAIN plans pganalyze collects, from auto_explain or from plans uploaded to a Workbook, and flags optimization opportunities with a suggested fix: inefficient nested loops, the wrong index chosen because of ORDER BY, inefficient index use, late join filters with OR, and disk spills from a low work_mem. Suggestions are query rewrites and planner hints that you can benchmark in Workbooks before changing production; the algorithms are deterministic, built on Postgres planner behavior. Query Advisor is on the Scale and Enterprise plans.
Does pganalyze have an MCP server, and does it work with Claude Code, Codex, and Cursor?
Yes. The pganalyze MCP Server works with Claude Code, Codex, Cursor, and any other MCP client that supports HTTP transport, such as Claude Desktop, AWS DevOps Agent or VS Code. In Claude Code, add it with claude mcp add --transport http pganalyze https://app.pganalyze.com/mcp. It gives the assistant read access to the query statistics, EXPLAIN plans, Index Advisor results, issues, and logs that pganalyze already collected, so it can investigate a slow query without touching your database. It is in public preview on all current plans.
Does the pganalyze MCP Server run queries against my database?
No. The pganalyze MCP Server never opens a connection to your database. It answers from the statistics, EXPLAIN plans, and logs the collector already sent to pganalyze. You authorize each client through OAuth: basic read access is always granted, full read and Workbook write access are separate opt-ins, and the server never sees more than your own account permissions allow. Admins can switch it off for the whole organization. The one write path is the optional Workbooks tools: EXPLAIN ANALYZE through the collector's separate role, on a query you approve, with a 60-second timeout and parameter values you choose.
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.