mirror of
https://github.com/hanzoai/postgres.git
synced 2026-10-30 18:33:07 +00:00
Robert Haas
25520d1a34
This one occurs when an outer join appears beneath the made-unique side of a semijoin. The issue is that join RTEs are not featured out of sj_unique_rels entries. Fix, and add a test case. Reported-by: Alexander Lakhin <[email protected]> Analyzed-by: Tender Wang <[email protected]> Discussion: http://postgr.es/m/[email protected]
427 lines
14 KiB
Plaintext
427 lines
14 KiB
Plaintext
LOAD 'pg_plan_advice';
|
|
SET max_parallel_workers_per_gather = 0;
|
|
CREATE TABLE sj_wide (
|
|
id integer primary key,
|
|
val1 integer,
|
|
padding text storage plain
|
|
) WITH (autovacuum_enabled = false);
|
|
INSERT INTO sj_wide
|
|
SELECT g, g%10+1, repeat(' ', 300) FROM generate_series(1, 1000) g;
|
|
CREATE INDEX ON sj_wide (val1);
|
|
VACUUM ANALYZE sj_wide;
|
|
CREATE TABLE sj_narrow (
|
|
id integer primary key,
|
|
val1 integer
|
|
) WITH (autovacuum_enabled = false);
|
|
INSERT INTO sj_narrow
|
|
SELECT g, g%10+1 FROM generate_series(1, 1000) g;
|
|
CREATE INDEX ON sj_narrow (val1);
|
|
VACUUM ANALYZE sj_narrow;
|
|
-- We expect this to make the VALUES list unique and use index lookups to
|
|
-- find the rows in sj_wide, so as to avoid a full scan of sj_wide.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_wide
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
-----------------------------------------------------------
|
|
Nested Loop
|
|
-> HashAggregate
|
|
Group Key: "*VALUES*".column1, "*VALUES*".column2
|
|
-> Values Scan on "*VALUES*"
|
|
-> Index Scan using sj_wide_pkey on sj_wide
|
|
Index Cond: (id = "*VALUES*".column1)
|
|
Filter: (val1 = "*VALUES*".column2)
|
|
Generated Plan Advice:
|
|
JOIN_ORDER("*VALUES*" sj_wide)
|
|
NESTED_LOOP_PLAIN(sj_wide)
|
|
INDEX_SCAN(sj_wide public.sj_wide_pkey)
|
|
SEMIJOIN_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_wide "*VALUES*")
|
|
(13 rows)
|
|
|
|
-- If we ask for a unique semijoin, we should get the same plan as with
|
|
-- no advice. If we ask for a non-unique semijoin, we should see a Semi
|
|
-- Join operation in the plan tree.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique("*VALUES*")';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_wide
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
-----------------------------------------------------------
|
|
Nested Loop
|
|
-> HashAggregate
|
|
Group Key: "*VALUES*".column1, "*VALUES*".column2
|
|
-> Values Scan on "*VALUES*"
|
|
-> Index Scan using sj_wide_pkey on sj_wide
|
|
Index Cond: (id = "*VALUES*".column1)
|
|
Filter: (val1 = "*VALUES*".column2)
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE("*VALUES*") /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER("*VALUES*" sj_wide)
|
|
NESTED_LOOP_PLAIN(sj_wide)
|
|
INDEX_SCAN(sj_wide public.sj_wide_pkey)
|
|
SEMIJOIN_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_wide "*VALUES*")
|
|
(15 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_non_unique("*VALUES*")';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_wide
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
------------------------------------------------------------------------------------------
|
|
Hash Semi Join
|
|
Hash Cond: ((sj_wide.id = "*VALUES*".column1) AND (sj_wide.val1 = "*VALUES*".column2))
|
|
-> Seq Scan on sj_wide
|
|
-> Hash
|
|
-> Values Scan on "*VALUES*"
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_NON_UNIQUE("*VALUES*") /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_wide "*VALUES*")
|
|
HASH_JOIN("*VALUES*")
|
|
SEQ_SCAN(sj_wide)
|
|
SEMIJOIN_NON_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_wide "*VALUES*")
|
|
(13 rows)
|
|
|
|
COMMIT;
|
|
-- Because this table is narrower than the previous one, a sequential scan
|
|
-- is less expensive, and we choose a straightforward Semi Join plan by
|
|
-- default. (Note that this is also very sensitive to the length of the IN
|
|
-- list, which affects how many index lookups the alternative plan will need.)
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_narrow
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------
|
|
Hash Semi Join
|
|
Hash Cond: ((sj_narrow.id = "*VALUES*".column1) AND (sj_narrow.val1 = "*VALUES*".column2))
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> Values Scan on "*VALUES*"
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow "*VALUES*")
|
|
HASH_JOIN("*VALUES*")
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_NON_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_narrow "*VALUES*")
|
|
(11 rows)
|
|
|
|
-- Here, we expect advising a unique semijoin to swith to the same plan that
|
|
-- we got with sj_wide, and advising a non-unique semijoin should not change
|
|
-- the plan.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique("*VALUES*")';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_narrow
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------
|
|
Hash Join
|
|
Hash Cond: ((sj_narrow.id = "*VALUES*".column1) AND (sj_narrow.val1 = "*VALUES*".column2))
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> HashAggregate
|
|
Group Key: "*VALUES*".column1, "*VALUES*".column2
|
|
-> Values Scan on "*VALUES*"
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE("*VALUES*") /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow "*VALUES*")
|
|
HASH_JOIN("*VALUES*")
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_narrow "*VALUES*")
|
|
(15 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_non_unique("*VALUES*")';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM sj_narrow
|
|
WHERE (id, val1) IN (VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5));
|
|
QUERY PLAN
|
|
----------------------------------------------------------------------------------------------
|
|
Hash Semi Join
|
|
Hash Cond: ((sj_narrow.id = "*VALUES*".column1) AND (sj_narrow.val1 = "*VALUES*".column2))
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> Values Scan on "*VALUES*"
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_NON_UNIQUE("*VALUES*") /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow "*VALUES*")
|
|
HASH_JOIN("*VALUES*")
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_NON_UNIQUE("*VALUES*")
|
|
NO_GATHER(sj_narrow "*VALUES*")
|
|
(13 rows)
|
|
|
|
COMMIT;
|
|
-- In the above example, we made the outer side of the join unique, but here,
|
|
-- we should make the inner side unique.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
------------------------------------------
|
|
Hash Join
|
|
Hash Cond: (g.g = sj_narrow.val1)
|
|
-> Function Scan on generate_series g
|
|
-> Hash
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(g sj_narrow)
|
|
HASH_JOIN(sj_narrow)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(13 rows)
|
|
|
|
-- We should be able to force a plan with or without the make-unique strategy,
|
|
-- with either side as the driving table.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique(sj_narrow)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
--------------------------------------------
|
|
Hash Join
|
|
Hash Cond: (g.g = sj_narrow.val1)
|
|
-> Function Scan on generate_series g
|
|
-> Hash
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE(sj_narrow) /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(g sj_narrow)
|
|
HASH_JOIN(sj_narrow)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(15 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_non_unique(sj_narrow)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
------------------------------------------------
|
|
Hash Semi Join
|
|
Hash Cond: (g.g = sj_narrow.val1)
|
|
-> Function Scan on generate_series g
|
|
-> Hash
|
|
-> Seq Scan on sj_narrow
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_NON_UNIQUE(sj_narrow) /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(g sj_narrow)
|
|
HASH_JOIN(sj_narrow)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_NON_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(13 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique(sj_narrow) join_order(sj_narrow)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
------------------------------------------------
|
|
Hash Join
|
|
Hash Cond: (sj_narrow.val1 = g.g)
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> Function Scan on generate_series g
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE(sj_narrow) /* matched */
|
|
JOIN_ORDER(sj_narrow) /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow g)
|
|
HASH_JOIN(g)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(16 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_non_unique(sj_narrow) join_order(sj_narrow)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
------------------------------------------------
|
|
Hash Right Semi Join
|
|
Hash Cond: (sj_narrow.val1 = g.g)
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> Function Scan on generate_series g
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_NON_UNIQUE(sj_narrow) /* matched */
|
|
JOIN_ORDER(sj_narrow) /* matched */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow g)
|
|
HASH_JOIN(g)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_NON_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(14 rows)
|
|
|
|
COMMIT;
|
|
-- However, mentioning the wrong side of the join should result in an advice
|
|
-- failure.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique(g)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
--------------------------------------------
|
|
Nested Loop
|
|
Disabled: true
|
|
Join Filter: (g.g = sj_narrow.val1)
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
-> Function Scan on generate_series g
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE(g) /* matched, failed */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow g)
|
|
NESTED_LOOP_PLAIN(g)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(15 rows)
|
|
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_non_unique(g)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
------------------------------------------------
|
|
Nested Loop
|
|
Disabled: true
|
|
Join Filter: (g.g = sj_narrow.val1)
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
-> Function Scan on generate_series g
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_NON_UNIQUE(g) /* matched, failed */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(sj_narrow g)
|
|
NESTED_LOOP_PLAIN(g)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(15 rows)
|
|
|
|
COMMIT;
|
|
-- Test conflicting advice.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique(sj_narrow) semijoin_non_unique(sj_narrow)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g
|
|
WHERE g in (select val1 from sj_narrow);
|
|
QUERY PLAN
|
|
---------------------------------------------------------------------
|
|
Hash Join
|
|
Hash Cond: (g.g = sj_narrow.val1)
|
|
-> Function Scan on generate_series g
|
|
-> Hash
|
|
-> HashAggregate
|
|
Group Key: sj_narrow.val1
|
|
-> Seq Scan on sj_narrow
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE(sj_narrow) /* matched, conflicting */
|
|
SEMIJOIN_NON_UNIQUE(sj_narrow) /* matched, conflicting, failed */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(g sj_narrow)
|
|
HASH_JOIN(sj_narrow)
|
|
SEQ_SCAN(sj_narrow)
|
|
SEMIJOIN_UNIQUE(sj_narrow)
|
|
NO_GATHER(g sj_narrow)
|
|
(16 rows)
|
|
|
|
COMMIT;
|
|
-- Try applying SEMIJOIN_UNIQUE() to a non-semijoin.
|
|
BEGIN;
|
|
SET LOCAL pg_plan_advice.advice = 'semijoin_unique(g)';
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM generate_series(1,1000) g, sj_narrow s WHERE g = s.val1;
|
|
QUERY PLAN
|
|
----------------------------------------------------------
|
|
Merge Join
|
|
Merge Cond: (s.val1 = g.g)
|
|
-> Index Scan using sj_narrow_val1_idx on sj_narrow s
|
|
-> Sort
|
|
Sort Key: g.g
|
|
-> Function Scan on generate_series g
|
|
Supplied Plan Advice:
|
|
SEMIJOIN_UNIQUE(g) /* matched, inapplicable, failed */
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(s g)
|
|
MERGE_JOIN_PLAIN(g)
|
|
INDEX_SCAN(s public.sj_narrow_val1_idx)
|
|
NO_GATHER(g s)
|
|
(13 rows)
|
|
|
|
COMMIT;
|
|
-- Test the case where the subquery containing a semijoin is removed from
|
|
-- the query entirely; this test is just to make sure that advice generation
|
|
-- does not fail.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT * FROM
|
|
(SELECT * FROM sj_narrow WHERE id IN (SELECT val1 FROM sj_wide)
|
|
LIMIT 1) x,
|
|
LATERAL (SELECT 1 WHERE false) y;
|
|
QUERY PLAN
|
|
--------------------------
|
|
Result
|
|
Replaces: Scan on x
|
|
One-Time Filter: false
|
|
Generated Plan Advice:
|
|
NO_GATHER(x)
|
|
(5 rows)
|
|
|
|
-- Test the case where the planner makes one side of a semijoin unique, and
|
|
-- that side contains an outer join; this test is just to make sure that
|
|
-- advice generation does not fail.
|
|
EXPLAIN (COSTS OFF, PLAN_ADVICE)
|
|
SELECT 1 FROM generate_series(1, 1000) g WHERE EXISTS
|
|
(SELECT 1 FROM
|
|
(SELECT 1 FROM (SELECT 1) LEFT JOIN sj_narrow ON true) s,
|
|
sj_narrow t2 WHERE g = t2.id);
|
|
QUERY PLAN
|
|
------------------------------------------------------------------------
|
|
Hash Join
|
|
Hash Cond: (t2.id = g.g)
|
|
-> Unique
|
|
-> Nested Loop
|
|
-> Index Only Scan using sj_narrow_pkey on sj_narrow t2
|
|
-> Materialize
|
|
-> Nested Loop Left Join
|
|
-> Result
|
|
-> Seq Scan on sj_narrow
|
|
-> Hash
|
|
-> Function Scan on generate_series g
|
|
Generated Plan Advice:
|
|
JOIN_ORDER(t2 ("*RESULT*" sj_narrow) g)
|
|
NESTED_LOOP_PLAIN(sj_narrow)
|
|
NESTED_LOOP_MATERIALIZE((sj_narrow "*RESULT*"))
|
|
HASH_JOIN(g)
|
|
SEQ_SCAN(sj_narrow)
|
|
INDEX_ONLY_SCAN(t2 public.sj_narrow_pkey)
|
|
SEMIJOIN_UNIQUE((t2 sj_narrow "*RESULT*"))
|
|
NO_GATHER(g t2 sj_narrow "*RESULT*")
|
|
(20 rows)
|
|
|