← Back to the library
BackendDeep dive · 9 min

A query that was fast gets slow as the table grows

“An endpoint that took milliseconds for months now takes seconds, or got slow overnight with no deploy; the SQL is unchanged, but the table is bigger or its data distribution shifted.”

ERROR CODES YOU MAY SEE

A code is a clue. Use its meaning and surrounding evidence to narrow the cause.

57014PostgreSQL SQLSTATE · platform

The query was canceled (query_canceled).

Read the accompanying message to identify the cancellation cause. Check timeouts and query traces; cancellation alone does not establish N+1 access.

Official reference for 57014 (opens in new tab)Code definition reviewed

THE PRINCIPLE

Compare estimated rows with actual rows: a large gap means the planner is working from wrong information, and the fix is better information, not hints.

FIRST MOVES

  1. Capture the real plan with EXPLAIN (ANALYZE, BUFFERS) using production-like parameters; it executes the statement, so wrap writes in BEGIN ... ROLLBACK.
  2. Find the node where estimated rows and actual rows diverge, and where shared read (not hit) buffers dominate.
  3. Check last_analyze, last_autoanalyze and n_mod_since_analyze in pg_stat_user_tables; run ANALYZE after bulk loads, and manually on partitioned parents, which autovacuum does not analyze.
  4. Add an index that matches the filter and sort (composite, in column order), built with CREATE INDEX CONCURRENTLY.
  5. If only some parameter values are slow in a prepared statement, compare EXPLAIN EXECUTE plans: after five executions PostgreSQL may switch to a generic plan; plan_cache_mode can force custom plans.

TOOLS: THEN → NOW

EXPLAIN estimates onlyEXPLAIN (ANALYZE, BUFFERS), plus pg_stat_statements and auto_explain to catch regressions in production
Planner hints or disabling scan typesFresh statistics, extended statistics for correlated columns, and indexes shaped like the query

PATTERN SNAPSHOT

-- 1. What actually happens (ANALYZE runs the query)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

-- 2. Are statistics stale?
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'orders';
ANALYZE orders;

-- 3. Give the planner an index shaped like the filter + sort
CREATE INDEX CONCURRENTLY orders_customer_created
  ON orders (customer_id, created_at DESC);

CLOSE THE AI. EXPLAIN THIS.

The plan estimates 12 rows and the node actually returns 400,000. What two causes do you check first, and why does adding a planner hint not fix either?

HOW IT WORKS UNDERNEATH

SOURCES

Guide reviewed