Slow database queries: distinguish bad plans, lock waits and storage pressure

TL;DR: Find where the elapsed time goes before changing the database. A query can spend its time executing an inefficient plan, waiting for another transaction or waiting on resources; those failures need different repairs.

Establish which operation became slow

Start with one affected operation and its parameters, time window and deployment history. Compare database execution time with the application's connection-acquisition and network time. A request that waits two seconds for a pool lease and runs SQL in ten milliseconds is primarily a connection-pool investigation, even if the application labels the whole interval “database latency.”

For a running PostgreSQL query, inspect activity and waits before launching another expensive copy. The following read-only query excludes its own backend and avoids printing potentially sensitive SQL parameters. A monitoring role needs appropriate visibility into other sessions.

SELECT pid, state, wait_event_type, wait_event,
       clock_timestamp() - query_start AS query_age,
       pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database()
  AND state = 'active'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

An active session can be waiting. PostgreSQL's activity statistics distinguish session state from wait events; a sample with no wait event does not prove sustained CPU saturation. Repeated observations, query identity and host evidence are stronger than one screenshot.

Follow the evidence to a specific repair

If a transaction holds a row lock while its application calls another service, adding CPU will not release that lock. Identify the owner and the business operation before cancelling anything. A cancellation can roll back work, and terminating one backend can make clients retry simultaneously. Repair transaction scope after restoring service. PostgreSQL documents blocking-session functions separately from resource statistics.

Reproduce a selective lookup in a disposable database

This PostgreSQL exercise creates a temporary table, so disconnecting removes it. The numbers describe synthetic data, not a benchmark. Run both plans and compare their work; do not expect identical timings or planner choices on every machine.

CREATE TEMP TABLE lookup_lab AS
SELECT n AS id, n % 1000 AS customer_id, repeat('x', 80) AS payload
FROM generate_series(1, 50000) AS n;
ANALYZE lookup_lab;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM lookup_lab WHERE customer_id = 417;
CREATE INDEX ON lookup_lab (customer_id);
ANALYZE lookup_lab;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM lookup_lab WHERE customer_id = 417;

There are 50 matching rows among 50,000. Without an index, the table scan checks the full relation. An index gives the planner a selective access path, commonly an index or bitmap scan for this fixture. Confirm the returned row count remains 50. Also query a condition that selects nearly the whole table: a sequential scan may then be the cheaper choice. “Sequential scan” alone is not a defect.

The EXPLAIN guide explains estimated costs, actual rows and buffer reporting. Costs are planner units, not milliseconds. Actual rows and times can be per-loop averages, so account for loops before judging a repeatedly executed node. A shared-buffer hit avoids a PostgreSQL buffer miss; a buffer read may still be served by the operating-system cache. It does not prove a physical disk access.

EXPLAIN ANALYZE executes the statement. Use the safe local SELECT above for learning; do not run an expensive or mutating production statement merely to obtain a plan. Even a transaction followed by rollback does not reverse every possible external side effect.

Check whether the improvement survives real parameters

A query may be selective for a small customer and return millions of rows for a large one. Compare representative parameter values and distributions, including prepared-statement behavior, before celebrating one fast result. An index also consumes space and adds write work. Add it when the supported workload benefits, then observe write latency and plan selection as well as read latency.

Self-check: a previously fast indexed lookup now waits 12 seconds, reports a lock wait and has one blocking session. Should the first action be another index? No. Find the transaction holding the conflicting lock and its owner. The existing plan may still be adequate; the elapsed time is currently dominated by waiting for permission to proceed.

For a distributed warehouse, Redshift query performance separates workload queueing from execution and follows redistribution evidence before recommending capacity.