rollback;
begin;

CREATE TABLE public.foo (
    id integer NOT NULL,
    tags text,
    is_cursed boolean
);
CREATE TABLE public.bar (
    id integer NOT NULL,
    foo_id integer
);
CREATE TABLE public.baz (
    id integer NOT NULL,
    bar_id integer,
    is_cool bool
);
CREATE TABLE public.baz_details (
    id integer NOT NULL,
    is_cool boolean
);
CREATE TABLE public.qux (
    id integer NOT NULL,
    foo_id integer
);

set random_page_cost to 1.2; -- I'm on an SSD
set work_mem to '400MB';
set parallel_setup_cost to 9999999999;

insert into foo(id) select generate_series(0, 999999);
analyze foo;
insert into bar(id, foo_id) select k, k from generate_series(0, 999999) k order by random();
analyze bar;
insert into baz(id, bar_id, is_cool) select k, k, k % 10000 = 0 from generate_series(0, 999999) k order by random();
analyze baz;
insert into qux(id, foo_id) select k, k from generate_series(0, 499999) k order by random();
analyze qux;

ALTER TABLE ONLY public.bar
    ADD CONSTRAINT bar_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.baz_details
    ADD CONSTRAINT baz_details_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.baz
    ADD CONSTRAINT baz_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.foo
    ADD CONSTRAINT foo_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.qux
    ADD CONSTRAINT qux_pkey PRIMARY KEY (id);
CREATE INDEX bar_foo_id_idx ON public.bar USING btree (foo_id);
CREATE INDEX baz_bar_id_idx ON public.baz USING btree (bar_id);
CREATE INDEX baz_is_cool_idx ON public.baz USING btree (is_cool);
--CREATE INDEX qux_foo_id_idx ON public.qux USING btree (foo_id);
ALTER TABLE ONLY public.bar
    ADD CONSTRAINT bar_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES public.foo(id);
ALTER TABLE ONLY public.baz
    ADD CONSTRAINT baz_bar_id_fkey FOREIGN KEY (bar_id) REFERENCES public.bar(id);

explain (analyze, verbose)
SELECT *
FROM qux
LEFT JOIN (
       SELECT bar.id,
              bar.foo_id,
              EXISTS (
               SELECT 1
               FROM baz
               WHERE baz.id = bar.id
                 AND is_cool
              ) has_baz
       FROM bar
) bb ON bb.foo_id = -qux.foo_id;
/*
Patched:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       QUERY PLAN                                                                        │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Hash Left Join  (cost=111226.94..125939.94 rows=500000 width=17) (actual time=504.718..751.087 rows=500000 loops=1)                                     │
│   Output: qux.id, qux.foo_id, bar.id, bar.foo_id, ((alternatives: SubPlan 1 or hashed SubPlan 2))                                                       │
│   Hash Cond: ((- qux.foo_id) = bar.foo_id)                                                                                                              │
│   ->  Seq Scan on public.qux  (cost=0.00..7213.00 rows=500000 width=8) (actual time=0.012..49.076 rows=500000 loops=1)                                  │
│         Output: qux.id, qux.foo_id                                                                                                                      │
│   ->  Hash  (cost=98726.94..98726.94 rows=1000000 width=9) (actual time=504.109..504.111 rows=1000000 loops=1)                                          │
│         Output: bar.id, bar.foo_id, ((alternatives: SubPlan 1 or hashed SubPlan 2))                                                                     │
│         Buckets: 1048576  Batches: 1  Memory Usage: 48232kB                                                                                             │
│         ->  Seq Scan on public.bar  (cost=3.36..98726.94 rows=1000000 width=9) (actual time=0.173..246.021 rows=1000000 loops=1)                        │
│               Output: bar.id, bar.foo_id, (alternatives: SubPlan 1 or hashed SubPlan 2)                                                                 │
│               SubPlan 1                                                                                                                                 │
│                 ->  Index Scan using baz_pkey on public.baz  (cost=0.42..2.84 rows=1 width=0) (never executed)                                          │
│                       Index Cond: (baz.id = bar.id)                                                                                                     │
│                       Filter: baz.is_cool                                                                                                               │
│               SubPlan 2                                                                                                                                 │
│                 ->  Index Scan using baz_is_cool_idx on public.baz baz_1  (cost=0.42..4.01 rows=67 width=4) (actual time=0.032..0.133 rows=100 loops=1) │
│                       Output: baz_1.id                                                                                                                  │
│                       Index Cond: (baz_1.is_cool = true)                                                                                                │
│ Planning Time: 0.377 ms                                                                                                                                 │
│ Execution Time: 775.263 ms                                                                                                                              │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Unpatched:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                              QUERY PLAN                                                              │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop Left Join  (cost=0.43..1664567.00 rows=500000 width=17) (actual time=0.020..1244.876 rows=500000 loops=1)                │
│   Output: qux.id, qux.foo_id, bar.id, bar.foo_id, ((alternatives: SubPlan 1 or hashed SubPlan 2))                                    │
│   ->  Seq Scan on public.qux  (cost=0.00..7213.00 rows=500000 width=8) (actual time=0.009..53.196 rows=500000 loops=1)               │
│         Output: qux.id, qux.foo_id                                                                                                   │
│   ->  Index Scan using bar_foo_id_idx on public.bar  (cost=0.43..3.30 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=500000) │
│         Output: bar.id, bar.foo_id, (alternatives: SubPlan 1 or hashed SubPlan 2)                                                    │
│         Index Cond: (bar.foo_id = (- qux.foo_id))                                                                                    │
│         SubPlan 1                                                                                                                    │
│           ->  Index Scan using baz_pkey on public.baz  (cost=0.42..2.84 rows=1 width=0) (actual time=0.043..0.043 rows=1 loops=1)    │
│                 Index Cond: (baz.id = bar.id)                                                                                        │
│                 Filter: baz.is_cool                                                                                                  │
│         SubPlan 2                                                                                                                    │
│           ->  Index Scan using baz_is_cool_idx on public.baz baz_1  (cost=0.42..4.67 rows=100 width=4) (never executed)              │
│                 Output: baz_1.id                                                                                                     │
│                 Index Cond: (baz_1.is_cool = true)                                                                                   │
│ Planning Time: 0.300 ms                                                                                                              │
│ Execution Time: 1270.421 ms                                                                                                          │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Forced the right plan with enable_nestloop = off:
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                       QUERY PLAN                                                                        │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Hash Left Join  (cost=2869425.00..2884138.00 rows=500000 width=17) (actual time=512.337..746.015 rows=500000 loops=1)                                   │
│   Output: qux.id, qux.foo_id, bar.id, bar.foo_id, ((alternatives: SubPlan 1 or hashed SubPlan 2))                                                       │
│   Hash Cond: ((- qux.foo_id) = bar.foo_id)                                                                                                              │
│   ->  Seq Scan on public.qux  (cost=0.00..7213.00 rows=500000 width=8) (actual time=0.020..46.338 rows=500000 loops=1)                                  │
│         Output: qux.id, qux.foo_id                                                                                                                      │
│   ->  Hash  (cost=2856925.00..2856925.00 rows=1000000 width=9) (actual time=510.738..510.739 rows=1000000 loops=1)                                      │
│         Output: bar.id, bar.foo_id, ((alternatives: SubPlan 1 or hashed SubPlan 2))                                                                     │
│         Buckets: 1048576  Batches: 1  Memory Usage: 48232kB                                                                                             │
│         ->  Seq Scan on public.bar  (cost=0.00..2856925.00 rows=1000000 width=9) (actual time=0.271..249.277 rows=1000000 loops=1)                      │
│               Output: bar.id, bar.foo_id, (alternatives: SubPlan 1 or hashed SubPlan 2)                                                                 │
│               SubPlan 1                                                                                                                                 │
│                 ->  Index Scan using baz_pkey on public.baz  (cost=0.42..2.84 rows=1 width=0) (never executed)                                          │
│                       Index Cond: (baz.id = bar.id)                                                                                                     │
│                       Filter: baz.is_cool                                                                                                               │
│               SubPlan 2                                                                                                                                 │
│                 ->  Index Scan using baz_is_cool_idx on public.baz baz_1  (cost=0.42..4.03 rows=67 width=4) (actual time=0.076..0.191 rows=100 loops=1) │
│                       Output: baz_1.id                                                                                                                  │
│                       Index Cond: (baz_1.is_cool = true)                                                                                                │
│ Planning Time: 0.279 ms                                                                                                                                 │
│ Execution Time: 770.012 ms                                                                                                                              │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
*/
