pg-checkup
Workspace
HomeReports
GitHub
Run on GitHub
Learn
Start hereToo many connectionsSlow queriesLocks and blocked queriesVacuum and bloatTransaction ID wraparoundReplication lag and slotsDisk full and WAL growthOut of memoryHigh CPU and I/OStale connectionsWhen to scale
Help
File a ticketRequest a featurePricing
PrivacyTerms
Docs/Slow queries
Sign inNew checkupC

Slow queries

Symptom: a page, job or report got slower, or the database is busy with no obvious cause.

Find the queries

With pg_stat_statements installed:

select round(total_exec_time) as total_ms, calls, round(mean_exec_time::numeric, 2) as mean_ms, left(query, 100) as query
from pg_stat_statements order by total_exec_time desc limit 10;

Sort by total_exec_time for the biggest overall cost, mean_exec_time for the slowest per call, and calls to spot N+1 patterns (thousands of tiny identical queries).

Read the plan

explain (analyze, buffers) select ...;

EXPLAIN ANALYZE really runs the statement. For writes, wrap it: begin; explain (analyze) update ...; rollback;.

You see It usually means
Seq Scan on a big table with a Filter Missing index for that filter
Estimated rows far from actual rows Stale statistics: ANALYZE. Correlated columns: CREATE STATISTICS
Sort Method: external merge Disk The sort spilled: raise work_mem for that role or query
Nested Loop with a huge loops count Bad join order or missing index on the inner side
Buffers: read much larger than hit Data isn't cached: index it, or the working set exceeds memory

Fix

  • create index concurrently on the filtered or joined columns. A partial index (where status = 'open') is smaller and faster when you query a small slice.
  • On SSD storage, set random_page_cost = 1.1 so the planner stops avoiding indexes.
  • A query that got slow right after a deploy or a bulk load is usually stale statistics: run ANALYZE.
  • Turn on auto_explain (auto_explain.log_min_duration = '500ms') to capture plans of slow queries as they happen.
PreviousToo many connectionsNextLocks and blocked queries