Slow Query Investigation and Index Tuning

Slow query investigation is the discipline of proving why a PostgreSQL statement is slow before changing schema, configuration, or application code. Index tuning is one of the main corrections, but it is not a reflex. A useful index changes the planner’s cheapest path for an important query without imposing more write, storage, and maintenance cost than the workload can afford.

In this performance capstone lesson, the outcome is practical: given a slow statement, you should be able to capture evidence, read the important parts of its execution plan, decide whether an index fits the predicate and ordering, verify the improvement on representative data, and leave a rollback path. This connects directly to the wider PostgreSQL course because tables, data types, joins, constraints, MVCC, VACUUM, and migrations all influence query speed.

How PostgreSQL Decides a Plan

PostgreSQL does not simply use an index because one exists. The planner estimates possible paths and chooses the lowest estimated cost. Those estimates come from relation size, column statistics, selectivity, correlation, available indexes, enabled join methods, memory settings, and the shape of the SQL. The executor then runs the chosen plan, and EXPLAIN (ANALYZE) shows both estimates and actual work.

The most important plan terms are concrete. A Seq Scan reads a table without using an index. An Index Scan walks an index and visits heap pages for the matching rows. An Index Only Scan can return data from the index when all needed columns are available and the visibility map says the heap page is known visible. A Bitmap Index Scan gathers many tuple locations and a Bitmap Heap Scan fetches the heap pages in a more efficient order. A Sort means rows were explicitly ordered after retrieval. Rows Removed by Filter often reveals that PostgreSQL had to inspect many candidates to find a few useful rows.

Indexes are physical data structures maintained with every insert, delete, and relevant update. A B-tree index is the default choice for equality, ranges, prefix ordering, and many ORDER BY patterns. A multicolumn B-tree is ordered left to right, so (customer_id, created_at) helps a query that constrains customer_id and orders by created_at, but it is usually much less useful for a query that only filters by created_at. A partial index stores only rows matching a predicate. An expression index stores computed values, such as lower(email). Covering indexes add non-key columns with INCLUDE so reads can avoid heap visits when visibility permits.

Investigation Anatomy

The basic workflow is stable. First, identify the exact normalized statement and the workload symptom: high mean latency, high total time, timeout spikes, lock waits, or excessive I/O. Then run the statement with representative parameters and EXPLAIN (ANALYZE, BUFFERS). Compare estimated rows with actual rows, identify the node consuming time or buffers, and map that node back to a predicate, join condition, grouping, or ordering requirement. Only then design an index whose leading columns match equality predicates first, range or ordering columns next, and optional included columns last.

The syntax used during investigation is small but precise. EXPLAIN without ANALYZE estimates a plan without running the query. ANALYZE runs it, so use a transaction and rollback for destructive statements. BUFFERS shows shared block hits and reads, making I/O visible. CREATE INDEX CONCURRENTLY avoids blocking normal writes for long periods, but it cannot run inside a transaction block and takes longer than a regular index build. DROP INDEX CONCURRENTLY is the matching rollback operation for many production cases.

Example 1: A Selective Lookup

Start with a support screen that fetches open tickets for one account. Without an index, PostgreSQL may read the whole table and discard almost every row.

CREATE TABLE support_ticket (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id bigint NOT NULL,
    status text NOT NULL,
    created_at timestamptz NOT NULL,
    subject text NOT NULL
);

INSERT INTO support_ticket (account_id, status, created_at, subject)
SELECT (g % 1000),
       CASE WHEN g % 5 = 0 THEN 'open' ELSE 'closed' END,
       now() - (g || ' minutes')::interval,
       'ticket ' || g
FROM generate_series(1, 50000) AS g;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, subject
FROM support_ticket
WHERE account_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 10;

The expected symptom before tuning is a plan that must inspect far more rows than it returns, commonly a sequential scan plus a sort. The useful index starts with the equality predicate and continues with the ordering column.

CREATE INDEX idx_support_ticket_account_status_created
ON support_ticket (account_id, status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, subject
FROM support_ticket
WHERE account_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 10;

After tuning, the expected behavior is an index scan that can find account 42’s open rows in descending time order and stop after 10. If the plan still chooses a sequential scan on a tiny development table, that is not automatically wrong; a small table can be cheaper to scan than to reach through an index. The test needs enough data to resemble the real selectivity.

Example 2: A Partial Index for a Hot Subset

If most tickets are closed, indexing every status may waste space. A partial index stores only open tickets and can be smaller, faster to maintain, and more cache-friendly.

CREATE INDEX idx_support_ticket_open_account_created
ON support_ticket (account_id, created_at DESC)
WHERE status = 'open';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, subject
FROM support_ticket
WHERE account_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 10;

The expected plan can use the partial index because the query predicate implies the index predicate. The trade-off is specificity. A query for closed tickets cannot use this partial index, and a query written with a parameter such as status = $1 may not always prove at planning time that the predicate is exactly status = 'open'. Partial indexes are excellent for stable, highly skewed subsets such as active rows, unprocessed jobs, non-deleted records, or recent alerts.

Example 3: Expression and Covering Choices

Another common slow query is case-insensitive email lookup. A normal index on email does not help if the query applies lower(email) to every row. The indexed expression must match the expression used by the query.

CREATE TABLE app_user (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    display_name text NOT NULL,
    last_login_at timestamptz
);

INSERT INTO app_user (email, display_name, last_login_at)
SELECT 'user' || g || '@example.com', 'User ' || g, now() - (g || ' hours')::interval
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_app_user_lower_email_include
ON app_user (lower(email)) INCLUDE (display_name, last_login_at);

SELECT id, display_name
FROM app_user
WHERE lower(email) = lower('USER42@example.com');

The deterministic query result includes the row for user42@example.com, with display_name equal to User 42. The index supports the lookup expression, and the included columns can reduce heap access for queries that need display data. The trade-off is write cost and index size. Including large or frequently changed columns can make updates slower and prevent HOT updates, so covering indexes should target high-value read paths rather than every possible column.

Design Choices and Trade-offs

Column order matters. Put equality columns first when they sharply narrow the search, then range or ordering columns that preserve the requested order. Avoid redundant indexes such as (account_id) and (account_id, status) unless both are independently justified by different high-value plans. Prefer one index that matches a real query shape over many indexes that each seem plausible in isolation.

Read improvement has a write price. Every index must be updated during writes, vacuumed, stored on disk, backed up, and considered by the planner. Extra indexes can slow bulk loads, increase replication lag, and extend maintenance windows. Index tuning is therefore workload tuning: a dashboard query running once per day may not deserve the same index budget as a checkout query running thousands of times per minute.

Statistics can be as important as indexes. If estimated rows differ wildly from actual rows, run ANALYZE, consider raising statistics targets for skewed columns, or create extended statistics for correlated predicates. An index cannot compensate for every bad estimate, especially in multi-column filters where values are not independent.

Failure Modes and Troubleshooting

A common failure is the unused index. The symptom is that EXPLAIN still shows a sequential scan after index creation. Causes include low selectivity, stale statistics, mismatched expressions, an incompatible sort direction, or a partial index predicate that the query does not imply. Diagnose with EXPLAIN (ANALYZE, BUFFERS), check pg_indexes, inspect row estimates versus actual rows, and run ANALYZE. Correct by rewriting the query to match the indexable expression, changing the index column order, or dropping the index if the sequential scan is genuinely cheaper.

Another failure is a fast query that becomes slow under writes. The symptom is increased insert or update latency after adding several indexes. The cause is index maintenance and larger WAL volume. Diagnose by comparing write latency, table and index sizes, and pg_stat_user_indexes usage. Correct by removing unused or overlapping indexes, using a narrower partial index, or moving rare reporting needs to a replica or summary table.

A third failure is a blocked migration. A regular CREATE INDEX can take locks that interfere with application traffic. The symptom is sessions waiting on locks during deployment. Diagnose with pg_stat_activity and lock views, and correct future migrations by using CREATE INDEX CONCURRENTLY for large live tables, scheduling maintenance windows, and keeping rollback commands ready. If a concurrent build fails, PostgreSQL may leave an invalid index that should be dropped and recreated after fixing the cause.

Security, Reliability, and Performance Implications

Performance investigation can expose sensitive literals in logs and plan captures. Share normalized query shapes when possible, redact tenant identifiers and personal data, and restrict access to extensions or views that reveal query text. Reliability depends on repeatability: use representative parameters, note table sizes, record the schema before and after, and test rollback. For production systems, prefer additive changes first, measure real traffic, then remove obsolete indexes in a separate deployment.

Hands-on Lab

Prerequisites: a PostgreSQL database where you can create tables and indexes, plus permission to run EXPLAIN. Use a scratch schema or disposable database.

1. Create the two tables and seed data from Examples 1 and 3.
2. Run the first ticket query with EXPLAIN (ANALYZE, BUFFERS) before adding any ticket index. Save the scan type, actual time, returned rows, and buffer counts.
3. Create idx_support_ticket_open_account_created and run the same plan again.
4. Verify that the tuned plan returns the same rows as the untuned query and performs less unnecessary scanning on representative data.
5. Run the case-insensitive email lookup and verify that it returns User 42.
6. Inspect index usage with pg_stat_user_indexes after executing the queries several times.

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname IN ('support_ticket', 'app_user')
ORDER BY relname, indexrelname;

Verification is successful when the result set is unchanged, the ticket query uses a path compatible with the new partial index for the open-ticket predicate, and the email lookup returns User 42. Cleanup is straightforward in a scratch database.

DROP TABLE IF EXISTS support_ticket;
DROP TABLE IF EXISTS app_user;

In a shared environment, replace the cleanup with DROP INDEX CONCURRENTLY for only the indexes you created, after confirming no other session depends on the lab objects.

Assessment Exercises

1. A query filters by tenant_id, status, and created_at > now() - interval '7 days', then orders by created_at DESC. Propose an index and explain which part of the query each column supports.
2. An index exists on email, but WHERE lower(email) = lower($1) still scans the table. Explain the cause and two possible corrections.
3. A partial index on unprocessed jobs is very fast, but job updates slow down. What evidence would you collect before keeping, narrowing, or dropping the index?
4. An execution plan estimates 10 rows but returns 100,000 rows. Explain how this affects index choice and what statistics-related diagnostics you would run.
5. Design a rollback plan for an index added to a busy production table. Include the command style, verification, and one risk during rollback.

Summary

Slow query work is evidence-driven. Find the exact statement, measure the real plan, understand estimates versus actual work, and choose indexes that match predicates, joins, and ordering. B-tree, multicolumn, partial, expression, and covering indexes are different tools with different write costs. Good tuning improves the important read path, preserves correctness, avoids unnecessary indexes, and leaves behind measurements that explain why the change belongs in the system.