Database indexes explained: a PostgreSQL guide to slow queries
Understand when a PostgreSQL index may help, why column order matters and what to check before changing a live database.
A database index gives PostgreSQL another way to find rows. It can reduce the work needed for a selective lookup, but creating an index does not guarantee that a query will use it or become faster. The query, data distribution, existing indexes and planner estimates all matter.
If one app screen is slow, do not begin with "add indexes everywhere." Begin with the query behind that screen and the work PostgreSQL plans to do. This guide uses PostgreSQL 18; other databases and versions have different details.
An index is an access path, not a promise
PostgreSQL's EXPLAIN documentation describes sequential scans, index scans and bitmap scans among the possible ways to read rows. The planner chooses a plan based on the query and estimates about the data.
A query returning a large part of a table may reasonably use a sequential scan. An index is not automatically better simply because it exists. Conversely, a selective filter may benefit from an index that lets the database avoid examining much of the table.
For a slow "orders for one customer" page, the question is therefore not whether the database has any indexes. It is whether an appropriate access path exists for that query, and whether the plan uses it profitably on representative data.
Start with EXPLAIN, then investigate the evidence
A plain EXPLAIN shows the planned operations and estimates. Its cost figures are planner units, not milliseconds. Do not report a cost reduction as a measured response-time improvement.
EXPLAIN ANALYZE actually executes the query and reports observed information alongside the estimates. That makes it useful, but it is not a harmless preview. Do not run it on an unfamiliar modifying query or an expensive production workload just to see what happens. Get an appropriate review and use a safe test environment first.
A useful investigation records:
- The query shape and representative parameter values.
- The approximate data size and distribution used in the test.
- The selected scan and any filtering, sorting or joins.
- Estimated versus observed rows when safely measured.
- End-to-end application latency separately from database plan costs.
This is a suggested review template. It helps prevent a fast test on a tiny table from being presented as proof about production.
Column order matters in a multicolumn index
Suppose a query filters by customer and order status. An illustrative index candidate might be:
CREATE INDEX orders_customer_status_idx
ON orders (customer_id, status);
This is an example to discuss and test, not a production recommendation. It assumes those columns and that table exist.
The PostgreSQL 18 multicolumn-index guide explains that B-tree indexes are most efficient when conditions constrain their leading columns. Equality conditions on the leading columns, followed by a range condition on the next column, can limit the scanned portion.
Do not turn that into the absolute rule "an index cannot be used unless its first column appears." PostgreSQL 18 describes skip-scan optimizations that can help some queries without a leading-column equality condition. Whether that is worthwhile depends on the data and planner decision. Test the real workload instead of depending on the exception.
Also avoid putting every filter into one very wide index. The manual recommends using multicolumn indexes sparingly. Different screens can need different access paths, and not every workload needs a combined index.
Check what constraints already created
Primary-key and unique constraints automatically create supporting unique B-tree indexes. Before adding an index on the same columns, inspect what is already there.
Foreign keys are different. PostgreSQL does not automatically create an index on the referencing, or child-side, columns. Such an index may help when a parent update or delete must locate related rows, but usefulness still depends on the workload.
Our constraints guide explains the distinction between enforcing a relationship and choosing how to find its rows.
Creating an index has an operational cost
The CREATE INDEX manual explains that a normal index build blocks writes to the table while allowing reads. That can disrupt a live application even if the finished index would be useful.
CREATE INDEX CONCURRENTLY avoids locking out normal inserts, updates and deletes during the build, but it is not free or instant. It does more work, waits for relevant transactions and can add CPU and I/O load. A failed concurrent build can leave an invalid index that needs attention and still imposes update overhead. The concurrent form also cannot run inside a transaction block.
That is why a production change needs more than a copied SQL statement. Review the build method, expected load, available maintenance window, monitoring and failure recovery. This article is not a complete runbook for partitioned tables, unique concurrent builds or index removal.
What to ask before approving an index change
- Which slow query is this intended to help?
- What evidence connects the proposed columns and their order to that query?
- Is an equivalent supporting index already present?
- What happened in tests with representative data and parameters?
- How will the build affect live traffic, and who checks that it finishes validly?
- How will the team verify the user-visible result after release?
Do not promise a percentage speed-up in advance. A useful index change has a specific target, measured evidence and a safe rollout, rather than a larger index count.
By Omnistu. Reviewed against PostgreSQL 18 documentation on 16 November 2026. SQL examples are illustrative and have not been executed during this review.