PostgreSQL indexes: how to find and speed up slow queries

A slow PostgreSQL query can almost always be fixed with the right index, but you do not have to guess it: the EXPLAIN ANALYZE plan shows where the database reads unnecessary rows. The routine is simple — find slow queries with pg_stat_statements, read the plan, add an index matching the filter and sort order, and verify. Unneeded indexes, meanwhile, slow down writes.

Let us go through the routine step by step, with example queries and common mistakes.

Why do PostgreSQL queries become slow?

Queries become slow when the database has to read far more rows than the answer needs. While a table is small, a full scan goes unnoticed; once it holds millions of rows, the same query takes seconds.

Typical causes:

• no index matching the WHERE condition or the ORDER BY clause;
• an index exists but the condition is written so it cannot be used — for example, a function applied to the column;
• the application runs hundreds of small queries instead of one (the N+1 problem);
• stale statistics lead the planner to pick a poor plan;
• the query fetches extra columns or all rows when only one page is needed.

Indexes fix the first two causes. The rest are fixed in application code and database settings.

How do you find slow queries?

Find slow queries from database statistics, not gut feeling: the pg_stat_statements extension records execution time for every query, and you simply sort by total time. Often the top offender is not the slowest query but a fast one called millions of times.

-- Top queries by total execution time
SELECT
    round(total_exec_time::numeric / 1000, 1) AS total_sec,
    calls,
    round(mean_exec_time::numeric, 1)          AS avg_ms,
    left(query, 120)                           AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The extension has to be added to shared_preload_libraries and created with CREATE EXTENSION pg_stat_statements. Managed cloud databases usually provide it already. The log_min_duration_statement setting also helps: it logs every query that exceeds a threshold.

How do you read a query plan?

A query plan shows how PostgreSQL actually executed a query: which tables it read, by what method and how long each step took. EXPLAIN (ANALYZE, BUFFERS) runs the query and prints the real numbers.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

What to look for first:

1. Seq Scan on a large table — the database reads all of it; often a sign of a missing index.
2. Rows Removed by Filter — how many rows were read and thrown away; the more, the worse.
3. Sort over many rows — the sort is not using an index.
4. A gap between estimated rows and the actual count — a sign of stale statistics.

Photo: Unsplash

Which index should you create for a query?

Create the index to match how the query filters and sorts: equality columns first, then the sort or range column. For the query above, a composite index returns the right rows already in the right order.

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

CONCURRENTLY builds the index without blocking writes to the table — essential in production. Which index types fit which tasks:

Index type	What it is for	Example
B-tree (default)	Equality, ranges, sorting	A customer's orders by date
Composite	Several conditions plus sorting	(customer_id, status, created_at)
Partial	A condition on a small part of the table	Only new orders: WHERE status = 'new'
Expression	A condition with a function on a column	lower(email) for case-insensitive lookup
GIN	JSONB, arrays, full-text search	Searching fields inside JSONB
BRIN	Huge tables with time-ordered data	Logs and events by date

When an index is not used

An index will not help if the condition does not match how it was built. WHERE lower(email) = ... will not use an index on email — you need an expression index. A composite index on (customer_id, status) will not help a query that filters only by status. And if the condition selects most of the table, a full scan really is faster, and the planner is right.

Why do unnecessary indexes hurt?

Unnecessary indexes hurt because each one is updated on every insert and update, and takes up disk and memory. A table with a dozen indexes, half of them unused, accepts writes more slowly and takes longer to restore from backup.

How to keep indexes tidy:

• find unused indexes in pg_stat_user_indexes: their idx_scan column is zero;
• remove duplicates — an index on (a) is redundant if (a, b) exists;
• do not create indexes "just in case" — only for real queries from your statistics;
• run ANALYZE after bulk data changes to refresh statistics.

If you are still choosing a database, index capabilities are one of the arguments for PostgreSQL we covered in PostgreSQL vs MySQL.

In short

• Find slow queries with pg_stat_statements, sorted by total time.
• Read the plan with EXPLAIN (ANALYZE, BUFFERS): a Seq Scan and many discarded rows call for an index.
• Build composite indexes with equality columns first, then the sort or range column.
• In production, create indexes with CONCURRENTLY so writes are not blocked.
• Drop unused indexes — they slow writes and waste memory.