ES001: Selective sequential scan

• Signal: a Seq Scan (parallel or not) that keeps less than 5% of the rows it reads, on a table of at least 1,000 pages (or 50,000 rows per scan when the plan has no buffer counts), taking at least 10% of the runtime. Rows kept are those that pass the scan’s filter. When the scan is the inner side of a nested loop, they are the rows that pass the loop’s join filter.
• Evidence: rows kept of rows read, the filter, the table size in pages, loops, and the time in the node.
• Action: an index on the filtered columns. The finding says when the operator needs a trigram index (LIKE '%…'), text_pattern_ops (LIKE 'abc%') or GIN (@>, &&, @@). When the filter wraps the column in a cast or a function, an index on the column cannot help, and the action is to rewrite the condition or index the expression.
• Stays silent when: the table is small, the scan is cheap compared with the statement, a Limit, a semi or anti join, or a subquery can stop the scan early, or the filter ORs conditions on different columns (no single index serves it).

Example

The anti_join scenario of the test corpus, captured on PostgreSQL 16: NOT EXISTS subquery, planned as an anti join.

SELECT c.id
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded');

Hash Right Anti Join  (cost=656.00..5580.54 rows=17987 width=4) (actual time=31.147..33.114 rows=19800 loops=1)
  Output: c.id
  Inner Unique: true
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=270 read=2353
  I/O Timings: shared read=4.088
  ->  Seq Scan on public.orders o  (cost=0.00..4917.00 rows=2013 width=4) (actual time=0.066..21.837 rows=2000 loops=1)
        Output: o.id, o.customer_id, o.status, o.created_at, o.amount, o.note
        Filter: (o.status = 'refunded'::text)
        Rows Removed by Filter: 198000
        Buffers: shared hit=64 read=2353
        I/O Timings: shared read=4.088
  ->  Hash  (cost=406.00..406.00 rows=20000 width=4) (actual time=7.994..7.996 rows=20000 loops=1)
        Output: c.id
        Buckets: 32768  Batches: 1  Memory Usage: 960kB
        Buffers: shared hit=206
        ->  Seq Scan on public.customers c  (cost=0.00..406.00 rows=20000 width=4) (actual time=0.008..3.140 rows=20000 loops=1)
              Output: c.id
              Buffers: shared hit=206
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=239
Planning Time: 1.089 ms
Execution Time: 33.808 ms

explainsql reports:

HIGH  ES001 Selective sequential scan
Seq Scan on orders o reads 200,000 rows to keep 2,000.
Rows kept: 2,000 of 200,000 (1.0%)
Filter: (o.status = 'refunded'::text)
Table size: 2,417 pages (18.9 MB)
Time in the node: 21.8 ms (65% of the runtime)
→ An index on orders (status) would let PostgreSQL read only the matching rows.

All rules