ES005: Expensive nested-loop inner side

• Signal: a Nested Loop whose inner side runs at least twice. The work it repeats (everything but producing the outer rows) must take at least half of the loop’s time, and the loop at least 10% of the runtime. The inner side must also be a sequential scan, or a scan that removes at least ten times the rows it keeps.
• Evidence: inner loops, inner time per loop, the repeated work, and the rows removed per loop.
• Action: an index on the inner side’s join key, taken from the join filter or from the inner scan’s conditions on the outer side. The finding points to ES002 when the planner expected far fewer outer rows.
• Stays silent when: a Materialize or Memoize caches the inner side.

Example

The lateral_join_top_n scenario of the test corpus, captured on PostgreSQL 16: LATERAL top-3 per customer; without an index on customer_id, every loop walks the created_at index backwards and discards most of the rows it reads.

SELECT c.id, o.id, o.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, created_at
    FROM orders
    WHERE orders.customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) o
WHERE c.id <= 20;

Nested Loop  (cost=0.71..90994.50 rows=60 width=16) (actual time=1.612..312.552 rows=60 loops=1)
  Output: c.id, orders.id, orders.created_at
  Buffers: shared hit=1109606
  ->  Index Only Scan using customers_pkey on public.customers c  (cost=0.29..4.64 rows=20 width=4) (actual time=0.016..0.052 rows=20 loops=1)
        Output: c.id
        Index Cond: (c.id <= 20)
        Heap Fetches: 0
        Buffers: shared hit=3
  ->  Limit  (cost=0.42..4549.46 rows=3 width=12) (actual time=4.412..15.616 rows=3 loops=20)
        Output: orders.id, orders.created_at
        Buffers: shared hit=1109603
        ->  Index Scan Backward using orders_created_at_idx on public.orders  (cost=0.42..15163.90 rows=10 width=12) (actual time=4.410..15.612 rows=3 loops=20)
              Output: orders.id, orders.created_at
              Filter: (orders.customer_id = c.id)
              Rows Removed by Filter: 55201
              Buffers: shared hit=1109603
Settings: max_parallel_workers_per_gather = '0'
Planning:
  Buffers: shared hit=216
Planning Time: 0.619 ms
Execution Time: 312.622 ms

explainsql reports:

HIGH  ES005 Expensive nested-loop inner side
Nested Loop repeats Index Scan using orders_created_at_idx on orders 20 times; that takes 100% of the loop's time.
Inner loops: 20
Inner side per loop: 15.6 ms
Repeated work: 312.5 ms of the loop's 312.6 ms, 312.3 ms of it in the inner scans
Rows removed per loop: 55,201 by the filter of Index Scan using orders_created_at_idx on orders
→ An index on orders (customer_id) would turn each of the 20 inner scans into an index lookup.

All rules