Index Scan in PostgreSQL EXPLAIN

What it does

Index Scan searches an index (usually a B-tree) for the entries that match the conditions it can use, shown as Index Cond. For each entry it follows the pointer to the table (the heap) and fetches the row, then checks any remaining conditions, shown as Filter.

Rows come out in the index’s order. That’s often why the planner chooses it: ORDER BY created_at with an index on created_at needs no sort, and with a LIMIT it can stop after a few rows. Index Scan Backward is the same thing read from the other end, for DESC.

Each matching row means a visit to the table. If the rows are next to each other on disk, those visits hit the same few pages. If they’re scattered, each one can be a different page, which is why an index scan that returns many rows can be slower than reading the whole table.

When the planner picks it

• Few rows match: a primary key lookup, a selective condition.
• The order is useful: ORDER BY on the indexed column, especially with LIMIT, or a Merge Join that needs sorted input.
• The table’s physical order follows the index (high correlation in pg_stats), so even a range of thousands of rows touches few pages. Rows inserted in time order and indexed on their timestamp are the classic case.
• Inside a Nested Loop, as the inner side, looked up once per outer row with the outer value as the key (Index Cond: (id = l.product_id)).

For a medium share of rows in no useful order, the planner usually prefers a Bitmap Heap Scan, which reads each table page once.

Reading its numbers

Index Scan using orders_created_at_idx on orders  (cost=0.42..59.39 rows=1398 width=34) (actual time=0.016..0.185 rows=1440.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-02 00:00:00+00'::timestamp with time zone))
  Index Searches: 1
  Buffers: shared hit=19

• using orders_created_at_idx: which index.
• Index Cond: conditions used to search the index. Only these narrow down what’s read.
• Filter (not in this plan): conditions checked after the row is fetched. Every row it rejects was still fetched, and is counted in Rows Removed by Filter. A big number here means the index doesn’t cover the query’s conditions. See Rows Removed by Filter.
• Index Searches (PostgreSQL 18): how many times the scan descended the index, summed over all loops. A plain range is 1. id IN (10, 500000, 900000) is 3. A skip scan (new in 18) searches repeatedly, about once per distinct value of the column it skips: 7 searches for three status values in the example below.
• Buffers: index pages plus table pages. Here 1,440 rows cost only 19 pages because rows for one day sit together on disk.
• loops: as the inner side of a nested loop, rows and time are averages per lookup, and Buffers is the total. See loops and actual time.

When it’s a problem

• Large Rows Removed by Filter. The index finds candidates and the filter throws most of them away. Add the filtered column to the index (a multicolumn index, equality columns first), or use a partial index (CREATE INDEX … WHERE status = 'refunded') if the query always has that condition.
• Many rows, scattered across the table. Every row is a separate table visit. Grouping all 1,000,000 orders by customer through the customer_id index took 1,000,000 buffer accesses and 830 ms in our test (see GroupAggregate); a covering index that let PostgreSQL skip the table made it 236 ms. If you only need a few columns, an Index Only Scan avoids the table altogether.
• ORDER BY … LIMIT walking the wrong index. The planner may scan an index in order hoping to find matching rows soon; if they’re all at the far end, it reads almost everything. See Limit for a real case and the fix.
• Estimates are off. If rows= estimated and actual differ by orders of magnitude, run ANALYZE, then check the plan again.

Build new indexes on production tables with CREATE INDEX CONCURRENTLY: see CREATE INDEX CONCURRENTLY.

Example

PostgreSQL 18.6, default settings. orders gets one row per minute from 1 January 2024, so its physical order follows created_at:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE orders;

(Our test table also had a foreign key to customers; it doesn’t affect these plans.)

One day of orders:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02';

Index Scan using orders_created_at_idx on orders  (cost=0.42..59.39 rows=1398 width=34) (actual time=0.016..0.185 rows=1440.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-02 00:00:00+00'::timestamp with time zone))
  Index Searches: 1
  Buffers: shared hit=19
Planning Time: 0.054 ms
Execution Time: 0.238 ms

Three scattered ids, three searches:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE id IN (10, 500000, 900000);

Index Scan using orders_pkey on orders  (cost=0.42..17.33 rows=3 width=34) (actual time=0.172..0.324 rows=3.00 loops=1)
  Index Cond: (id = ANY ('{10,500000,900000}'::bigint[]))
  Index Searches: 3
  Buffers: shared hit=3 read=9

An index used for part of the condition, and a filter for the rest. This is the inner part of a join from our tests, refunded orders in one week:

Index Scan using orders_created_at_idx on orders o  (cost=0.42..393.75 rows=273 width=18) (actual time=0.053..1.776 rows=300.00 loops=1)
  Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
  Filter: (status = 'refunded'::text)
  Rows Removed by Filter: 9780
  Index Searches: 1
  Buffers: shared hit=115

It fetched 10,080 rows to keep 300. At this size that’s fine (1.8 ms); on a bigger range, an index on (status, created_at) would read only the refunded rows.

Skip scan: PostgreSQL 17 against 18

With an index on (status, total) and a query on total alone, 17 can’t use the index because the first column isn’t constrained. 18 can, by searching the index once per status value:

CREATE INDEX orders_status_total_idx ON orders (status, total);
EXPLAIN (ANALYZE, BUFFERS) SELECT id, status, total FROM orders WHERE total = 500.00;

PostgreSQL 17.11:

Gather  (cost=1000.00..14543.33 rows=10 width=22) (actual time=16.344..29.242 rows=10 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=2594 read=5740
  ->  Parallel Seq Scan on orders  (cost=0.00..13542.33 rows=4 width=22) (actual time=8.927..24.548 rows=3 loops=3)
        Filter: (total = 500.00)
        Rows Removed by Filter: 333330
        Buffers: shared hit=2594 read=5740
Planning:
  Buffers: shared hit=20 read=1
Planning Time: 0.450 ms
Execution Time: 29.274 ms

PostgreSQL 18.6:

Index Scan using orders_status_total_idx on orders  (cost=0.42..44.58 rows=10 width=22) (actual time=0.266..0.402 rows=10.00 loops=1)
  Index Cond: (total = 500.00)
  Index Searches: 7
  Buffers: shared hit=10 read=23
Planning:
  Buffers: shared hit=16 read=4
Planning Time: 0.258 ms
Execution Time: 0.422 ms

Skip scan works best when the skipped leading column has few distinct values, as status does here. An index that starts with the column you filter on is still the better choice when you can have one.

In Inlet

Inlet draws EXPLAIN and EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. Its structure editor adds indexes and shows the DDL it will run.