A slow PostgreSQL query does not automatically need an index. It may be reading too many rows, sorting a large result, using stale statistics, applying a function that prevents index use, or joining tables in an expensive order. The fastest way to choose the right fix is to inspect what PostgreSQL actually does.

This guide shows a repeatable workflow for diagnosing slow SQL with EXPLAIN ANALYZE, designing useful indexes, and confirming that the change improves the workload without adding unnecessary write overhead.

The example query

Assume a multi-tenant application stores customer orders:

CREATE TABLE orders (
    id bigserial PRIMARY KEY,
    tenant_id bigint NOT NULL,
    customer_id bigint NOT NULL,
    status text NOT NULL,
    total_cents integer NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}',
    created_at timestamptz NOT NULL DEFAULT now()
);

The dashboard needs the 50 most recent paid orders for one tenant:

SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

This query may be fast with a few thousand rows and become noticeably slower as the table grows. Do not guess which index to add. Capture the execution plan first.

1. Start with EXPLAIN, then use ANALYZE carefully

Plain EXPLAIN shows the plan PostgreSQL expects to use without running the statement:

EXPLAIN
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Add ANALYZE to execute the query and compare estimated rows with actual rows. BUFFERS adds information about cache and disk-page activity:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;
EXPLAIN ANALYZE really executes the statement. Be careful with INSERT, UPDATE, and DELETE. Test writes inside a transaction and roll them back, or use a safe staging environment.
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '2 years';
ROLLBACK;

2. Read the plan from the inside out

An execution plan is a tree. PostgreSQL runs the deepest nodes first and sends their rows to parent nodes. Focus on these signals:

  • Seq Scan: PostgreSQL reads the table sequentially. This is not always bad; it can be optimal when most rows are needed or the table is small.
  • Index Scan: PostgreSQL follows an index and fetches matching table rows.
  • Index Only Scan: the required values can come from the index, subject to visibility-map checks.
  • Bitmap Index Scan and Bitmap Heap Scan: useful when many scattered rows match.
  • Sort: look for large row counts, memory use, and disk spill.
  • Rows Removed by Filter: a high value can reveal that PostgreSQL reads far more rows than it returns.
  • actual time: time spent reaching and completing each node.
  • rows and loops: multiply them mentally; a cheap node repeated thousands of times may dominate the query.
  • shared hit versus shared read: hits came from PostgreSQL's buffer cache, while reads required blocks to be loaded.

Suppose the plan shows a sequential scan over millions of rows followed by a sort, while only 50 rows are returned. That is a strong sign that one index could support both filtering and ordering.

3. Refresh planner statistics before judging indexes

PostgreSQL uses table statistics to estimate selectivity and choose a plan. If estimated rows differ dramatically from actual rows, update statistics:

ANALYZE orders;

Autovacuum normally runs ANALYZE automatically, but bulk imports, large data changes, or an unusual distribution can leave estimates temporarily inaccurate. Diagnose index usage only after the planner has representative statistics.

You can compare estimated and actual row counts in the plan. A persistent difference of several orders of magnitude may justify higher per-column statistics for a heavily skewed column, but do not raise statistics targets across the whole database without evidence.

4. Design a composite index around the query

For common B-tree indexes, equality filters usually belong before a range or ordering column. The dashboard query filters by tenant_id and status, then orders by created_at:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at DESC);

This index allows PostgreSQL to locate one tenant and status, then read rows in the required order. Because the query has LIMIT 50, the database can stop after finding 50 matching entries instead of sorting the entire result.

Column order matters. An index on (created_at, tenant_id, status) is usually less useful for this query because the leading timestamp does not narrow the equality predicates first.

Why use CONCURRENTLY in production?

A regular CREATE INDEX blocks writes while the index is built. CREATE INDEX CONCURRENTLY allows normal writes to continue, making it safer for a busy production table. The tradeoff is a slower build with more work, and it cannot run inside a transaction block.

After creating the index, update statistics and capture a new plan:

ANALYZE orders;


EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

5. Add INCLUDE columns only when they help

A covering index can store non-key columns with INCLUDE:

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_cover
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, total_cents);

This may enable an index-only scan because all selected columns are available from the index. However, a larger index consumes more disk, memory, and write bandwidth. Index-only scans also depend on table visibility information, so frequently updated tables may still require heap access.

Start with the narrowest index that fixes the measured problem. Add included columns only after the plan shows that heap access is still a meaningful cost.

6. Use partial indexes for a valuable subset

A partial index stores only rows that satisfy a predicate. Suppose support staff frequently query pending orders, while pending rows represent a small fraction of the table:

CREATE INDEX CONCURRENTLY idx_orders_pending_tenant_created
ON orders (tenant_id, created_at DESC)
WHERE status = 'pending';

The smaller index can be faster to scan and cheaper to maintain than indexing every status. PostgreSQL can use it only when the query condition logically implies the index predicate:

SELECT id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Partial indexes are excellent for states such as pending jobs, active subscriptions, unprocessed events, or non-deleted records. They are less suitable when the selected subset is large or changes so often that maintenance becomes expensive.

7. Match the index type to the operator

B-tree is the default and covers equality, ranges, and ordered retrieval. PostgreSQL also provides specialized index types:

  • GIN: commonly used for JSONB containment, arrays, and full-text search.
  • GiST: useful for geometric, range, nearest-neighbor, and extension-defined operators.
  • BRIN: compact and effective for very large physically ordered tables, such as append-only time-series data.
  • Hash: supports equality comparisons, though B-tree is usually the more flexible default.

For a JSONB containment query:

SELECT id
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

A GIN index is a suitable candidate:

CREATE INDEX CONCURRENTLY idx_orders_metadata_gin
ON orders USING GIN (metadata);

Choose the index type from the actual operator used by the query. An index that exists but does not support the operator cannot help.

8. Use expression indexes for transformed values

An ordinary index on email may not support a query that filters on lower(email). Create an index on the same expression:

CREATE INDEX CONCURRENTLY idx_users_lower_email
ON users (lower(email));
SELECT id, email
FROM users
WHERE lower(email) = lower('Developer@example.com');

The query expression and index expression must match. Avoid applying functions or implicit casts to indexed columns unless the index was designed for them.

9. Know when PostgreSQL should ignore an index

An unused index does not necessarily indicate a problem. PostgreSQL may correctly choose a sequential scan when:

  • The table is small enough that reading it once is cheaper.
  • The query returns a large percentage of the table.
  • The filter has low selectivity, such as a boolean value shared by most rows.
  • Statistics suggest the index would require too many random heap reads.
  • The result is already cached and a sequential path is inexpensive.

Do not disable sequential scans as a permanent fix. That hides the planner's decision instead of correcting the schema, statistics, or query.

10. Find expensive queries before tuning them

Tuning a query that runs once a month may matter less than improving a moderately slow query executed thousands of times per minute. In production, use pg_stat_statements to identify SQL with high total execution time, high mean latency, or large call counts.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Enabling the extension may require server configuration and a restart, depending on the installation. Test the change and follow your managed database provider's instructions.

11. Measure index cost, not just query speed

Every index has a price:

  • INSERT, UPDATE, and DELETE operations must maintain it.
  • It consumes disk and buffer-cache space.
  • Vacuum and backup work increases.
  • Overlapping indexes create operational noise.

Review index usage:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

A low idx_scan count is a clue, not permission to delete an index. Unique constraints, infrequent critical reports, recently reset statistics, standby workloads, and seasonal jobs can all explain low usage. Confirm dependencies and observe a representative period before removal.

Why an expected index is not used

  • Stale statistics: run ANALYZE and recheck estimates.
  • Wrong column order: align leading columns with common equality predicates.
  • Query returns too many rows: a sequential scan may be cheaper.
  • Function or cast mismatch: use a matching expression index or correct parameter types.
  • Partial predicate mismatch: the query must imply the partial index condition.
  • Different ordering: verify ASC/DESC combinations in a multicolumn index.
  • Small test dataset: the planner may prefer a sequential scan until realistic data volume exists.
  • Prepared statement behavior: a generic plan may differ from a plan optimized for one parameter value.

A practical optimization workflow

  1. Identify a high-impact query from application tracing or pg_stat_statements.
  2. Save the exact SQL and realistic parameter values.
  3. Run EXPLAIN (ANALYZE, BUFFERS) safely.
  4. Compare estimates, actual rows, loops, filters, sorts, and buffer activity.
  5. Run ANALYZE if statistics may be stale.
  6. Design the narrowest index that supports the filter, join, and ordering pattern.
  7. Build it concurrently on a busy production table.
  8. Capture the new plan and compare execution time and buffers.
  9. Measure write latency and index size after the change.
  10. Document why the index exists and which query depends on it.

Production checklist

  • Use realistic data and parameters when testing plans.
  • Remember that EXPLAIN ANALYZE executes the statement.
  • Refresh statistics before diagnosing planner decisions.
  • Place equality columns before range or ordering columns in common B-tree designs.
  • Use partial, expression, GIN, GiST, or BRIN indexes only for matching workloads.
  • Prefer CREATE INDEX CONCURRENTLY when writes must continue.
  • Re-run the same plan with BUFFERS after each change.
  • Monitor index size and write overhead.
  • Do not remove an index based only on one statistics snapshot.
  • Keep database migrations for indexes reviewable and reversible.
The best PostgreSQL index is not the widest or most clever one. It is the smallest index that measurably improves an important query without imposing more cost than the workload can justify.

References