--
-- step by step, demo that it doesnt work:
--
dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders) NESTED_LOOP_PLAIN(orders) SEQ_SCAN(lineitem) INDEX_SCAN(orders public.pk_orders) GATHER(lineitem) SEMIJOIN_UNIQUE(lineitem) NO_GATHER(orders)';
SET
dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
ERROR:  unique semijoin found for relids (b 3) but not observed during planning
dbt3=#


-- so with max_parallel_workers_per_gather=2 works (-> NO ERROR)
set pg_plan_advice.advice = 'JOIN_ORDER(orders lineitem)
   NESTED_LOOP_PLAIN(lineitem)
   SEQ_SCAN(orders)
   INDEX_ONLY_SCAN(lineitem public.lineitem_l_orderkey_l_suppkey_idx)
   GATHER_MERGE((orders lineitem))
   SEMIJOIN_NON_UNIQUE(lineitem)';

-- when set to 0, this fails:
set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)';

-- note the SEMIJOIN_UNIQUE(lineitem) vs SEMIJOIN_NON_UNIQUE(lineitem), as it is going to be important later

---
--- FRESH
---
dbt3=# explain (costs off,plan_advice) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=0
NOTICE:  not a dupllicate, appending "(b 3)" to pps->sj_unique_rels
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Sort
         Sort Key: orders.o_orderpriority
         ->  Nested Loop
               ->  HashAggregate
                     Group Key: lineitem.l_orderkey
                     ->  Seq Scan on lineitem
                           Filter: (l_commitdate < l_receiptdate)
               ->  Index Scan using pk_orders on orders
                     Index Cond: (o_orderkey = lineitem.l_orderkey)
                     Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
 Generated Plan Advice:
   JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)
(19 rows)

--
-- quick sanity check if we really see the error (copy/paste from above):
--
dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)';
SET
dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
ERROR:  unique semijoin found for relids (b 3) but not observed during planning
dbt3=#

-- yes , we do so "b 3" seems to be HashAggregate/Group Key: lineitem.l_orderkey
-- so how's that NOT observed during planning if it is there?

dbt3=# LOAD 'pg_overexplain';
LOAD
dbt3=# explain (costs off, DEBUG) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: orders.o_orderpriority
   Disabled Nodes: 0
   Parallel Safe: false
   Plan Node ID: 0
   ->  Sort
         Sort Key: orders.o_orderpriority
         Disabled Nodes: 0
         Parallel Safe: false
         Plan Node ID: 1
         ->  Nested Loop
               Disabled Nodes: 0
               Parallel Safe: false
               Plan Node ID: 2
               ->  HashAggregate
                     Group Key: lineitem.l_orderkey
                     Disabled Nodes: 0
                     Parallel Safe: false
                     Plan Node ID: 3
                     ->  Seq Scan on lineitem
                           Filter: (l_commitdate < l_receiptdate)
                           Disabled Nodes: 0
                           Parallel Safe: false
                           Plan Node ID: 4
               ->  Index Scan using pk_orders on orders
                     Index Cond: (o_orderkey = lineitem.l_orderkey)
                     Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
                     Disabled Nodes: 0
                     Parallel Safe: false
                     Plan Node ID: 5
                     extParam: 0
                     allParam: 0
 PlannedStmt:
   Command Type: select
   Flags: canSetTag
   Subplans Needing Rewind: none
   Relation OIDs: 17089 17061
   Executor Parameter Types: 20
   Parse Location: 0 to end
(39 rows)

dbt3=# explain (costs off, RANGE_TABLE) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
                                                                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Sort
         Sort Key: orders.o_orderpriority
         ->  Nested Loop
               ->  HashAggregate
                     Group Key: lineitem.l_orderkey
                     ->  Seq Scan on lineitem
                           Filter: (l_commitdate < l_receiptdate)
                           Scan RTI: 3
               ->  Index Scan using pk_orders on orders
                     Index Cond: (o_orderkey = lineitem.l_orderkey)
                     Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
                     Scan RTI: 1
 RTI 1 (relation, in-from-clause):
   Eref: orders (o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment)
   Relation: orders
   Relation Kind: relation
   Relation Lock Mode: AccessShareLock
   Permission Info Index: 1
 RTI 2 (group):
   Eref: "*GROUP*" (o_orderpriority)
 RTI 3 (relation, in-from-clause):
   Eref: lineitem (l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment)
   Relation: lineitem
   Relation Kind: relation
   Relation Lock Mode: AccessShareLock
   Permission Info Index: 2
 Unprunable RTIs: 1 3
(29 rows)

dbt3=#

--
-- 3x conflicts (still with using SEMIJOIN_UNIQUE):
--
dbt3=# explain (costs off,plan_advice) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=0
NOTICE:  not a dupllicate, appending "(b 3)" to pps->sj_unique_rels
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Sort
         Sort Key: orders.o_orderpriority
         ->  Nested Loop
               ->  HashAggregate
                     Group Key: lineitem.l_orderkey
                     ->  Seq Scan on lineitem
                           Filter: (l_commitdate < l_receiptdate)
               ->  Index Scan using pk_orders on orders
                     Index Cond: (o_orderkey = lineitem.l_orderkey)
                     Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
 Supplied Plan Advice:
   SEQ_SCAN(lineitem) /* matched */
   INDEX_SCAN(orders public.pk_orders) /* matched */
   JOIN_ORDER(lineitem orders) /* matched, conflicting */
   NESTED_LOOP_PLAIN(orders) /* matched, conflicting */
   SEMIJOIN_UNIQUE(lineitem) /* matched, conflicting */
   NO_GATHER(orders) /* matched */
   NO_GATHER(lineitem) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)
(27 rows)

dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
ERROR:  unique semijoin found for relids (b 3) but not observed during planning
dbt3=#


-- still if I remove SEMIJOIN_UNIQUE(liteitem) it complains:
dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   NO_GATHER(orders lineitem)';
SET
dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
ERROR:  unique semijoin found for relids (b 3) but not observed during planning
dbt3=#


-- not shown here but also using SEMIJOIN_NON_UNIQUE was failing , which made me struggle


-- so back to parallel plan:

dbt3=# set pg_plan_advice.advice = '';
SET
dbt3=# set max_parallel_workers_per_gather to 2;
SET
dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Gather Merge
         Workers Planned: 2
         ->  Partial GroupAggregate
               Group Key: orders.o_orderpriority
               ->  Sort
                     Sort Key: orders.o_orderpriority
                     ->  Nested Loop Semi Join
                           ->  Parallel Seq Scan on orders
                                 Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
                           ->  Index Only Scan using lineitem_l_orderkey_l_suppkey_idx on lineitem
                                 Index Cond: (l_orderkey = orders.o_orderkey)
(13 rows)

dbt3=# explain (costs off, plan_advice) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=0
NOTICE:  not a dupllicate, appending "(b 3)" to pps->sj_unique_rels
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Gather Merge
         Workers Planned: 2
         ->  Partial GroupAggregate
               Group Key: orders.o_orderpriority
               ->  Sort
                     Sort Key: orders.o_orderpriority
                     ->  Nested Loop Semi Join
                           ->  Parallel Seq Scan on orders
                                 Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
                           ->  Index Only Scan using lineitem_l_orderkey_l_suppkey_idx on lineitem
                                 Index Cond: (l_orderkey = orders.o_orderkey)
 Generated Plan Advice:
   JOIN_ORDER(orders lineitem)
   NESTED_LOOP_PLAIN(lineitem)
   SEQ_SCAN(orders)
   INDEX_ONLY_SCAN(lineitem public.lineitem_l_orderkey_l_suppkey_idx)
   GATHER_MERGE((orders lineitem))
   SEMIJOIN_NON_UNIQUE(lineitem)



-- back to max_parallel_workers_per_gather=0 and explain VERBOSE
-- this shows that indeed we have Nested Loop // Inner Unique: true
dbt3=# explain (costs off, VERBOSE, plan_advice) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=0
NOTICE:  not a dupllicate, appending "(b 3)" to pps->sj_unique_rels
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
                                                                                                                                                                                          QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Output: orders.o_orderpriority, count(*)
   Group Key: orders.o_orderpriority
   ->  Sort
         Output: orders.o_orderpriority
         Sort Key: orders.o_orderpriority
         ->  Nested Loop
               Output: orders.o_orderpriority
               Inner Unique: true
               ->  HashAggregate
                     Output: lineitem.l_orderkey
                     Group Key: lineitem.l_orderkey
                     ->  Seq Scan on public.lineitem
                           Output: lineitem.l_orderkey, lineitem.l_partkey, lineitem.l_suppkey, lineitem.l_linenumber, lineitem.l_quantity, lineitem.l_extendedprice, lineitem.l_discount, lineitem.l_tax, lineitem.l_returnflag, lineitem.l_linestatus, lineitem.l_shipdate, lineitem.l_commitdate, lineitem.l_receiptdate, lineitem.l_shipinstruct, lineitem.l_shipmode, lineitem.l_comment
                           Filter: (lineitem.l_commitdate < lineitem.l_receiptdate)
               ->  Index Scan using pk_orders on public.orders
                     Output: orders.o_orderkey, orders.o_custkey, orders.o_orderstatus, orders.o_totalprice, orders.o_orderdate, orders.o_orderpriority, orders.o_clerk, orders.o_shippriority, orders.o_comment
                     Index Cond: (orders.o_orderkey = lineitem.l_orderkey)
                     Filter: ((orders.o_orderdate >= '1993-07-01'::date) AND (orders.o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
 Generated Plan Advice:
   JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)


-----
----- finally to realize that sj_unique_rtis is NULL there?
-----

dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)';
SET
dbt3=# explain (costs off, verbose) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  added SEMIJOIN_UNIQUE
NOTICE:  pgpa_plan_walker: walking over SEMIJOIN_UNIQUE features: 3, sj_unique_rtis=<>
ERROR:  unique semijoin found for relids (b 3) but not observed during planning


----------
----------
----------
dbt3=# set pg_plan_advice.advice = 'JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)';
SET
dbt3=#
dbt3=#
dbt3=# explain (costs off) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  added SEMIJOIN_UNIQUE
NOTICE:  pgpa_plan_walker: walking over SEMIJOIN_UNIQUE features: 3, sj_unique_rtis=<> sj_unique_rels=<>
ERROR:  unique semijoin found for relids (b 3) but not observed during planning
dbt3=#
dbt3=#
dbt3=# explain (costs off, plan_advice) SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= DATE '1993-07-01'
    AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH
    AND EXISTS (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            AND l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=0
NOTICE:  not a dupllicate, appending "(b 3)" to pps->sj_unique_rels
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=inner pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  jointype=outer pps_NULL?=0
NOTICE:  found=true! (ur->plan_name=(null) bms_ur_relids=3)
NOTICE:  found=1
NOTICE:  added SEMIJOIN_UNIQUE
WARNING:  could not dump unrecognized node type: 0
NOTICE:  pgpa_plan_walker: walking over SEMIJOIN_UNIQUE features: 3, sj_unique_rtis=((b 3)) sj_unique_rels=({})
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate
   Group Key: orders.o_orderpriority
   ->  Sort
         Sort Key: orders.o_orderpriority
         ->  Nested Loop
               ->  HashAggregate
                     Group Key: lineitem.l_orderkey
                     ->  Seq Scan on lineitem
                           Filter: (l_commitdate < l_receiptdate)
               ->  Index Scan using pk_orders on orders
                     Index Cond: (o_orderkey = lineitem.l_orderkey)
                     Filter: ((o_orderdate >= '1993-07-01'::date) AND (o_orderdate < '1993-10-01 00:00:00'::timestamp without time zone))
 Supplied Plan Advice:
   SEQ_SCAN(lineitem) /* matched */
   INDEX_SCAN(orders public.pk_orders) /* matched */
   JOIN_ORDER(lineitem orders) /* matched, conflicting */
   NESTED_LOOP_PLAIN(orders) /* matched, conflicting */
   SEMIJOIN_UNIQUE(lineitem) /* matched, conflicting */
   NO_GATHER(orders) /* matched */
   NO_GATHER(lineitem) /* matched */
 Generated Plan Advice:
   JOIN_ORDER(lineitem orders)
   NESTED_LOOP_PLAIN(orders)
   SEQ_SCAN(lineitem)
   INDEX_SCAN(orders public.pk_orders)
   SEMIJOIN_UNIQUE(lineitem)
   NO_GATHER(orders lineitem)
(27 rows)