LLM Best Practices

How to debug a slow Postgres query

Last updated 4 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Most slow Postgres queries come down to a missing or unusable index, stale statistics, a cold cache, or a query doing too much work. This guide finds the query with the largest total cost, reads its plan, fixes one cause, and confirms the fix. Plan-node meanings are in postgres-explain; index design rules are in postgres-indexes.

Prerequisites

  • Postgres 14 or newer; the SQL below works on current versions, including 18.
  • A role with pg_read_all_stats or superuser rights to read other users’ statements.
  • Production-shaped data. A ten-row dev table will not reproduce a production plan.

Steps

1. Enable pg_stat_statements

Add it to postgresql.conf (appending to any existing list) and restart; the library is only loaded at server start.

shared_preload_libraries = 'pg_stat_statements'

Then, once per database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. Rank by total time

Total time, not mean time, finds the queries worth tuning: a 2 ms query run a million times an hour costs more than one slow report.

SELECT calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(total_exec_time::numeric, 2) AS total_ms,
       rows, left(query, 100) AS query
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

Stored text is normalized with $1-style placeholders. Get real parameter values from the application log, or temporarily set log_min_duration_statement = 100 (milliseconds) and reload with SELECT pg_reload_conf();.

3. Read the plan

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents FROM orders
WHERE customer_id = 4291 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

ANALYZE runs the query, so wrap writes in a transaction and roll back. From Postgres 18, ANALYZE includes BUFFERS by default; keep it explicit for older servers. Read from the leaves up:

  • Sequential scan on a large table with a selective predicate: no usable index, or a function such as LOWER(email) = $1 hid the column. Index the expression.
  • actual rows far from the estimate: stale statistics. Run ANALYZE <table>, or raise default_statistics_target for that column.
  • High shared read on a second run: the working set does not fit in shared_buffers. Narrow the query or partition cold data (postgres-partitioning).

4. Add the right index

Equality columns first, then the range or sort column. INCLUDE adds payload columns for index-only scans.

CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
  ON orders (customer_id, status, created_at DESC) INCLUDE (total_cents);

CONCURRENTLY avoids a long write lock but cannot run inside a transaction block. Skip the index if an existing one already leads with the same columns.

5. Re-run the plan

Expect the new index in the plan (Index Only Scan using orders_customer_status_created_idx with Heap Fetches: 0 when it covers the query, an Index Scan or Bitmap Index Scan when few rows match) and shared hit with no read on a warm cache. If Heap Fetches is high, run VACUUM ANALYZE orders so the visibility map is current (postgres-vacuum).

6. Re-measure under real load

SELECT pg_stat_statements_reset();

Let the application run for an hour, then rerun the step 2 query. If it is back on the list, the planner chose a different plan for untested parameter values; capture those and repeat.

Verify it worked

EXPLAIN (ANALYZE, BUFFERS) <the query>;     -- new index, low actual time
SELECT pid, query FROM pg_stat_activity WHERE wait_event_type = 'Lock';   -- no blocked statements

Also confirm the route’s p95 latency dropped in the dashboard from observability.

Common errors

  • CREATE INDEX CONCURRENTLY cannot run inside a transaction block. Run it outside BEGIN.
  • The new index is unused. The predicate is not selective enough, so the planner prefers a sequential scan; run ANALYZE, and check that parameter types match the indexed column types.
  • Fast in dev, slow in prod. Statistics differ; compare pg_stats (n_distinct, most_common_vals) and run ANALYZE on production.
  • Mean time is low but p99 is high. A lock or I/O stall, not a plan problem; inspect pg_locks and pg_stat_activity.
  • An index never gets scanned. Check idx_scan in pg_stat_user_indexes after a week and drop unused ones; each index costs write time.

FAQ

Should I tune the slowest Postgres query or the one with the most total time?

Rank by total time. A 2 ms query run a million times an hour costs more than one slow report.

Does EXPLAIN ANALYZE change data?

It runs the query, so wrap writes in a transaction and roll back.

Why is Postgres not using my new index?

The predicate is not selective enough, statistics are stale, or parameter types do not match the indexed column types. Run ANALYZE first.

Can I run CREATE INDEX CONCURRENTLY inside a transaction?

No. Run it outside BEGIN; it fails inside a transaction block.

Backlinks