q4_errors_hard_without_stats_and_no_parallel_v2.txt
text/plain
Filename: q4_errors_hard_without_stats_and_no_parallel_v2.txt
Type: text/plain
Part: 0
Message:
Re: pg_plan_advice
--
-- 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)