BEGIN;


-- Driver: small → forces Nested Loop
CREATE TABLE driver (id int PRIMARY KEY);
INSERT INTO driver SELECT g FROM generate_series(1, 5) g;

-- Left (probe) side of the LEFT JOIN: large, NOT parameterized.
CREATE TABLE big_side (
    id   serial PRIMARY KEY,
    jkey int NOT NULL
);
INSERT INTO big_side (jkey)
    SELECT g % 200 FROM generate_series(1, 100000) g;

-- Right (hash build) side of the LEFT JOIN: parameterized by driver.id.
-- Many rows overall, but ~50 matching per drv_id.
CREATE TABLE small_side (
    id     serial PRIMARY KEY,
    drv_id int NOT NULL,
    jkey   int NOT NULL
);
INSERT INTO small_side (drv_id, jkey)
    SELECT (g % 5) + 1, g % 200
    FROM generate_series(1, 250) g;
CREATE INDEX ON small_side (drv_id);

ANALYZE driver;
ANALYZE big_side;
ANALYZE small_side;

SET enable_mergejoin = off;
SET enable_nestloop  = on;

EXPLAIN (ANALYZE, VERBOSE)
SELECT d.id, sub.total
FROM driver d
JOIN LATERAL (
    SELECT coalesce(sum(s.jkey), 0) AS total
    FROM big_side b
    LEFT JOIN small_side s ON s.jkey = b.jkey AND s.drv_id = d.id
) sub ON true;


RESET enable_mergejoin;
SET enable_hashjoin = off;

EXPLAIN (ANALYZE, VERBOSE)
SELECT d.id, sub.total
FROM driver d
JOIN LATERAL (
    SELECT coalesce(sum(s.jkey), 0) AS total
    FROM big_side b
    LEFT JOIN small_side s ON s.jkey = b.jkey AND s.drv_id = d.id
) sub ON true;

ROLLBACK;

/*
                                                                QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2447.12..12236.75 rows=5 width=12) (actual time=32.316..161.508 rows=5.00 loops=1)
   Output: d.id, (COALESCE(sum(s.jkey), '0'::bigint))
   Buffers: shared hit=2226
   ->  Seq Scan on public.driver d  (cost=0.00..1.05 rows=5 width=4) (actual time=0.004..0.005 rows=5.00 loops=1)
         Output: d.id
         Buffers: shared hit=1
   ->  Aggregate  (cost=2447.12..2447.13 rows=1 width=8) (actual time=32.299..32.299 rows=1.00 loops=5)
         Output: COALESCE(sum(s.jkey), '0'::bigint)
         Buffers: shared hit=2225
         ->  Hash Left Join  (cost=5.75..2197.12 rows=100000 width=4) (actual time=0.055..26.698 rows=105000.00 loops=5)
               Output: s.jkey
               Hash Cond: (b.jkey = s.jkey)
               Buffers: shared hit=2225
               ->  Seq Scan on public.big_side b  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.005..7.446 rows=100000.00 loops=5)
                     Output: b.id, b.jkey
                     Buffers: shared hit=2215
               ->  Hash  (cost=5.12..5.12 rows=50 width=4) (actual time=0.047..0.047 rows=50.00 loops=5)
                     Output: s.jkey
                     Buckets: 1024  Batches: 1  Memory Usage: 10kB
                     Buffers: shared hit=10
                     ->  Seq Scan on public.small_side s  (cost=0.00..5.12 rows=50 width=4) (actual time=0.005..0.038 rows=50.00 loops=5)
                           Output: s.jkey
                           Filter: (s.drv_id = d.id)
                           Rows Removed by Filter: 200
                           Buffers: shared hit=10
 Planning:
   Buffers: shared hit=76 read=1
 Planning Time: 0.426 ms
 Execution Time: 161.544 ms
(29 rows)

RESET
SET
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=10752.98..53766.03 rows=5 width=12) (actual time=39.478..112.316 rows=5.00 loops=1)
   Output: d.id, (COALESCE(sum(s.jkey), '0'::bigint))
   Buffers: shared hit=454
   ->  Seq Scan on public.driver d  (cost=0.00..1.05 rows=5 width=4) (actual time=0.003..0.004 rows=5.00 loops=1)
         Output: d.id
         Buffers: shared hit=1
   ->  Aggregate  (cost=10752.98..10752.99 rows=1 width=8) (actual time=22.461..22.461 rows=1.00 loops=5)
         Output: COALESCE(sum(s.jkey), '0'::bigint)
         Buffers: shared hit=453
         ->  Merge Right Join  (cost=9754.36..10502.98 rows=100000 width=4) (actual time=4.296..17.823 rows=105000.00 loops=5)
               Output: s.jkey
               Merge Cond: (s.jkey = b.jkey)
               Buffers: shared hit=453
               ->  Sort  (cost=6.54..6.66 rows=50 width=4) (actual time=0.040..0.042 rows=50.00 loops=5)
                     Output: s.jkey
                     Sort Key: s.jkey
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: shared hit=10
                     ->  Seq Scan on public.small_side s  (cost=0.00..5.12 rows=50 width=4) (actual time=0.005..0.033 rows=50.00 loops=5)
                           Output: s.jkey
                           Filter: (s.drv_id = d.id)
                           Rows Removed by Filter: 200
                           Buffers: shared hit=10
               ->  Sort  (cost=9747.82..9997.82 rows=100000 width=4) (actual time=4.254..6.945 rows=105000.00 loops=5)
                     Output: b.jkey
                     Sort Key: b.jkey
                     Sort Method: quicksort  Memory: 3073kB
                     Buffers: shared hit=443
                     ->  Seq Scan on public.big_side b  (cost=0.00..1443.00 rows=100000 width=4) (actual time=0.006..12.205 rows=100000.00 loops=1)
                           Output: b.jkey
                           Buffers: shared hit=443
 Planning:
   Buffers: shared hit=12
 Planning Time: 0.279 ms
 Execution Time: 112.400 ms
(35 rows)
*/