What is a database index, and why is my query slow?

The short answer: an index is a data structure the database keeps apart from the table so it can find matching rows without reading every row. In PostgreSQL, with no usable index, a query such as WHERE id = 42 has to scan the entire table, row by row. With an index on id, the system might only have to walk a few levels deep into a search tree.

Most slow queries are slow because they read far more data than they return. The way to see what PostgreSQL actually read is EXPLAIN. This post goes from how an index stores data, through reading a plan, to what you pay for having too many indexes. Examples and numbers come from the documentation for PostgreSQL 18, released on 25 September 2025.

SELECT * FROM orders WHERE id = 42;                             
                                                                
Seq Scan on orders    Filter: (id = 42)                         
                                                                
  [p1]  [p2]  [p3]  [p4]  [p5]  [p6]  [p7]  [p8]  [p9]  [p10]   
                                                                
heap pages read:  0 / 10                                        
                                                                
┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄
Index Scan using orders_pkey  Index Cond: (id = 42)             
                          [root] ●                              
             [1-40]       [41-80]       [81-120]                
                  [41-50]  [51-60]  [61-80]                     
                                                                
  [p1]  [p2]  [p3]  [p4]  [p5]  [p6]  [p7]  [p8]  [p9]  [p10]   
                                                                
index pages read: 0   heap pages read:  0 / 10                  
                                                                

What is a database index?

An index is a second copy of one or more column values (or an expression), kept in sorted order together with the location of each row in the table. Every index in PostgreSQL is a secondary index: it is stored separately from the table's main data area, which PostgreSQL calls the heap.

Creating one is a single statement:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

With no type given, CREATE INDEX builds a B-tree, which fits most cases. Hash, GiST, SP-GiST, GIN and BRIN are available with USING. After that there is nothing to maintain by hand: the system updates the index when the table is modified and uses it when the planner thinks it will be more efficient than a sequential scan.

How does PostgreSQL find rows without an index?

It runs a Seq Scan: it reads every page of the table in order and checks the condition against each row. Here is an example from PostgreSQL's own EXPLAIN chapter (Using EXPLAIN):

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;

 Seq Scan on tenk1  (cost=0.00..470.00 rows=7000 width=244)
   Filter: (unique1 < 7000)

The Filter line means the node reads each row and passes on only those that meet the condition. As the docs point out, the scan still has to visit all 10000 rows, so the cost does not drop; it goes up slightly for the extra checking.

A Seq Scan is not always wrong. If the query needs most of the table, reading pages in order is often the cheaper plan. The problem is a query that wants one row out of millions and still gets a Seq Scan.

How does a B-tree index store data?

A PostgreSQL B-tree is a multi-level tree structure made of:

• Leaf pages on the lowest level. Each leaf tuple points to a table row.
• Internal pages on every level above. Each tuple points to a page on the next level down.
• One metapage at a fixed position at the start of the index's first file.

The docs note that over 99% of all pages are typically leaf pages. When a leaf page is full it splits; when the root page itself cannot take another entry, a root split adds a new level to the tree. The number of levels grows much more slowly than the number of rows.

Because B-tree keys are sorted, the planner can use a B-tree for <, <=, =, >= and >, for BETWEEN, IN and IS NULL, and for LIKE 'foo%' when the pattern is anchored at the start. It cannot help with LIKE '%bar'. If your database does not use the C locale, a pattern-matching index needs a special operator class.

Why doesn't PostgreSQL use my index?

Because the planner picks the plan it estimates to be cheapest, and sometimes a Seq Scan really is cheaper. The usual reasons:

• The query wants many rows. A plain index scan reads both the index and the heap, and the rows the index points to might be anywhere in the heap, so those reads are random. random_page_cost, the planner's cost for a non-sequential page fetch, defaults to 4.0, against 1.0 for seq_page_cost.
• The table is small. For a table that fits in one disk page, you'll nearly always get a sequential scan plan whether indexes exist or not, because that page has to be read either way.
• The condition does not match the index. An index on email does nothing for WHERE lower(email) = '...'. That query needs an index on the expression lower(email).
• Statistics are missing or stale. The planner estimates row counts from the statistics ANALYZE collects. The docs put it bluntly: examining index usage without having run ANALYZE is a lost cause.

How do you read EXPLAIN output?

EXPLAIN prints the plan as a tree of nodes, one line per node, with each node's inputs indented below it. The bottom nodes are scan nodes that return raw rows from a table: sequential scans, index scans and bitmap index scans.

The cost numbers

In (cost=0.00..445.00 rows=10000 width=244) the values, left to right, are the estimated start-up cost before the first row comes out, the estimated total cost if the node runs to completion, the estimated number of rows output, and the average row width in bytes (Using EXPLAIN).

Costs are not milliseconds. They are arbitrary units determined by the planner's cost parameters. The docs show the arithmetic: tenk1 has 345 disk pages and 10000 rows, so the Seq Scan costs (345 × 1.0) + (10000 × 0.01) = 445, where 1.0 is seq_page_cost and 0.01 is cpu_tuple_cost.

Seq Scan, Index Scan and Bitmap Heap Scan

Narrow the condition to unique1 < 100 and the plan in the docs becomes two levels:

 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

The child node reads the index to find row locations. The parent sorts those locations into physical order before reading the heap, to cut the cost of separate fetches. For unique1 = 42, which returns one row, the planner picks a plain Index Scan, the plan type you'll most often see for queries that fetch just a single row.

Index Cond vs Filter

These two lines are the first thing I look for in a plan. Index Cond is a condition used to search the index. Filter is a condition checked on rows after they have been fetched. In the docs' example, WHERE unique1 < 100 AND stringu1 = 'xxx' can only use the index for unique1; stringu1 = 'xxx' is applied as a filter because the index covers only unique1. The row estimate drops, but the cost doesn't, because the same rows still have to be read.

What does EXPLAIN ANALYZE add?

EXPLAIN ANALYZE actually executes the query and shows the true row counts and run time of each node next to the estimates. The thing the docs say is usually most important to check is whether the estimated row counts are reasonably close to reality. If the planner expected 10 rows and got 50000, it chose its plan from wrong numbers.

Details worth knowing:

• actual time is in milliseconds, while cost is in planner units, so the two are unlikely to match up.
• loops: when a node runs more than once, such as the inner side of a nested loop, its time and rows are averages per execution. Multiply by loops yourself.
• Rows Removed by Filter counts rows the filter threw away. In the docs' example, a Seq Scan on ten < 7 returns 7000 rows and removes 3000. When this number is large next to the rows returned, the index usually does not cover that condition.
• Heap Fetches on an Index Only Scan counts how often the heap still had to be visited to check row visibility. An index-only scan is a win only if a significant fraction of the table's heap pages are marked all-visible in the visibility map.

One warning matters more than the rest: on an UPDATE or DELETE, EXPLAIN ANALYZE changes your data, because it runs the statement. To look at the plan only, wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE id = 42;
ROLLBACK;

And don't test on a small table and assume the result holds for the real one. The docs warn that results on a toy-sized table cannot be assumed to apply to large tables, because cost estimates are not linear.

How should columns be ordered in a multicolumn index?

Put the column you filter with = first, then the column you filter by range. PostgreSQL states the rule for multicolumn B-trees: equality constraints on the leading columns, plus any inequality on the first column without an equality constraint, are what limit the portion of the index that is scanned. Conditions on columns further right are checked inside the index, which saves heap visits, but they don't necessarily shrink the part of the index that gets read.

Take a common query, "one customer's orders since a date":

SELECT id, created_at FROM orders
WHERE customer_id = 1001 AND created_at >= '2026-01-01';

CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);

With (customer_id, created_at), the scan jumps to customer 1001 and reads one contiguous range of dates. Swap the order to (created_at, customer_id) and the scan has to read every entry from 1 January onward, for every customer, checking customer_id on each entry.

PostgreSQL 18 added skip scan, which lets a multicolumn B-tree help even when the query has no = on a leading column. The docs say the planner generally takes this approach only when there are so few distinct values in the leading column that most of the index can be skipped; with many distinct values it will usually prefer a sequential scan. Column order still matters. An index can have up to 32 columns; that is a limit, not a target.

What do indexes cost?

Indexes make reads faster and writes slower, and they take space.

• Every write updates the indexes too. The system has to keep each index synchronized with the table, which adds overhead to INSERT, UPDATE and DELETE, and indexes can prevent heap-only tuples (HOT). When an update can't use HOT, changing one column covered by one index needs a new index tuple in every index on the table, including indexes that don't cover that column.
• Building an index on a big table blocks writes. A normal CREATE INDEX lets SELECT continue, but INSERT, UPDATE and DELETE are blocked until the build finishes. On a live system, CREATE INDEX CONCURRENTLY avoids that lock, but it performs two scans of the table and takes significantly longer, cannot run inside a transaction block, and if it fails it leaves an INVALID index behind that queries ignore but writes still pay for.
• An unused index is pure cost. The docs advise that indexes seldom or never used in queries should be removed. The pg_stat_user_indexes view shows idx_scan (index scans initiated on the index) and last_idx_scan (The Cumulative Statistics System):

SELECT relname, indexrelname, idx_scan, last_idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Before dropping an index with a low idx_scan, I check that it isn't enforcing a unique constraint and that the statistics weren't reset recently.

How do you track down a slow query step by step?

This is the order I work in, from the cheapest check to the most involved:

1. Run ANALYZE on the table so the planner has fresh statistics (Examining Index Usage).
2. Run EXPLAIN ANALYZE on real data, or data of similar size. The docs say to use real data for experimentation: test data only tells you what indexes the test data needs.
3. Find the node where the time goes. Remember to multiply by loops.
4. Compare estimated rows with actual rows. If they are far apart, fix statistics before adding indexes.
5. Look at Filter and Rows Removed by Filter. Many discarded rows suggest the condition needs an index, or an extra column in an existing one.
6. If the planner ignores an index, test why. Turn off enable_seqscan in a test session. If it still won't use the index, the query condition probably does not match the index. If it does use it, time both plans: sometimes the planner was right.
7. Build the index with CONCURRENTLY on a live system, then run EXPLAIN ANALYZE again to confirm the plan changed.

A useful index matches a query you actually run often. An index on every column is not a strategy.

Sources

• PostgreSQL 18: 11.1 Introduction (Indexes)
• PostgreSQL 18: 11.2 Index Types
• PostgreSQL 18: 11.3 Multicolumn Indexes
• PostgreSQL 18: 11.7 Indexes on Expressions
• PostgreSQL 18: 11.9 Index-Only Scans and Covering Indexes
• PostgreSQL 18: 11.12 Examining Index Usage
• PostgreSQL 18: 14.1 Using EXPLAIN
• PostgreSQL 18: 19.7 Query Planning
• PostgreSQL 18: 27.2 The Cumulative Statistics System
• PostgreSQL 18: 65.1 B-Tree Indexes
• PostgreSQL 18: CREATE INDEX
• PostgreSQL 18 Released (25 September 2025)