ES006: Index scan that filters most rows

Signal: an Index Scan, Index Only Scan or Bitmap Heap Scan whose filter removes at least 90% of the rows found through the index. It must remove at least 100 rows per loop (or 1,000 over all loops), and the scan, including its index, must take at least 10% of the runtime.
Evidence: the index, the index condition, the filter, and rows removed of rows found.
Action: a composite index that covers the filtered columns as well as the index condition’s.

Example

The index_scan_filter scenario of the test corpus, captured on PostgreSQL 16: index range scan on created_at that discards most rows with a filter on status.

SELECT * FROM orders
WHERE created_at >= timestamptz '2024-06-01 00:00:00+00'
  AND created_at < timestamptz '2024-06-15 00:00:00+00'
  AND status = 'refunded';

Bitmap Heap Scan on public.orders  (cost=77.05..2671.77 rows=37 width=64) (actual time=0.254..2.127 rows=27 loops=1)
  Output: id, customer_id, status, created_at, amount, note
  Recheck Cond: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
  Filter: (orders.status = 'refunded'::text)
  Rows Removed by Filter: 3809
  Heap Blocks: exact=319
  Buffers: shared hit=332
  ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..77.04 rows=3662 width=0) (actual time=0.181..0.182 rows=3836 loops=1)
        Index Cond: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
        Buffers: shared hit=13
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=130
Planning Time: 0.423 ms
Execution Time: 2.173 ms

explainsql reports:

HIGH  ES006 Index scan that filters most rows
Bitmap Heap Scan on orders finds 3,836 rows through orders_created_at_idx and its filter throws away 3,809.
Index: orders_created_at_idx
Index condition: ((orders.created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (orders.created_at < '2024-06-15 00:00:00+00'::timestamp with time zone))
Filter: (orders.status = 'refunded'::text)
Rows removed by the filter: 3,809 of 3,836 found
→ A composite index on orders (status, created_at) would let the index do the filtering.

All rules

Keyboard shortcuts

ExplainSQL

ES006: Index scan that filters most rows

Example