Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit364d200

Browse files
Marina Polyakovakovdb75
Marina Polyakova
authored andcommitted
Avoid making commutatively-duplicate clauses in EquivalenceClasses.
See the commit a5fc46414deb7cbcd4cec1275efac69b9ac10500 (Avoid makingcommutatively-duplicate clauses in EquivalenceClasses.) in PostgreSQL 16.
1 parente32efa8 commit364d200

File tree

4 files changed

+1038
-0
lines changed

4 files changed

+1038
-0
lines changed

‎expected/pathman_join_clause_4.out

Lines changed: 161 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,161 @@
1+
/*
2+
* Since 8edd0e794 (>= 12) Append nodes with single subplan are eliminated,
3+
* causing different output; pathman_gaps_1.out is the updated version.
4+
*/
5+
\set VERBOSITY terse
6+
SET search_path = 'public';
7+
CREATE SCHEMA pathman;
8+
CREATE EXTENSION pg_pathman SCHEMA pathman;
9+
CREATE SCHEMA test;
10+
/*
11+
* Test push down a join clause into child nodes of append
12+
*/
13+
/* create test tables */
14+
CREATE TABLE test.fk (
15+
id1 INT NOT NULL,
16+
id2 INT NOT NULL,
17+
start_key INT,
18+
end_key INT,
19+
PRIMARY KEY (id1, id2));
20+
CREATE TABLE test.mytbl (
21+
id1 INT NOT NULL,
22+
id2 INT NOT NULL,
23+
key INT NOT NULL,
24+
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES test.fk(id1, id2),
25+
PRIMARY KEY (id1, key));
26+
SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
27+
create_hash_partitions
28+
------------------------
29+
8
30+
(1 row)
31+
32+
/* ...fill out with test data */
33+
INSERT INTO test.fk VALUES (1, 1);
34+
INSERT INTO test.mytbl VALUES (1, 1, 5), (1, 1, 6);
35+
/* gather statistics on test tables to have deterministic plans */
36+
ANALYZE;
37+
/* run test queries */
38+
EXPLAIN (COSTS OFF) /* test plan */
39+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
40+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
41+
WHERE NOT key <@ int4range(6, end_key);
42+
QUERY PLAN
43+
-------------------------------------------------------------------------------------------------------
44+
Nested Loop
45+
-> Seq Scan on fk
46+
-> Custom Scan (RuntimeAppend)
47+
Prune by: (m.id1 = fk.id1)
48+
-> Seq Scan on mytbl_0 m
49+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
50+
-> Seq Scan on mytbl_1 m
51+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
52+
-> Seq Scan on mytbl_2 m
53+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
54+
-> Seq Scan on mytbl_3 m
55+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
56+
-> Seq Scan on mytbl_4 m
57+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
58+
-> Seq Scan on mytbl_5 m
59+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
60+
-> Seq Scan on mytbl_6 m
61+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
62+
-> Seq Scan on mytbl_7 m
63+
Filter: ((id1 = fk.id1) AND (fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
64+
(20 rows)
65+
66+
/* test joint data */
67+
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
68+
FROM test.mytbl m JOIN test.fk USING(id1, id2)
69+
WHERE NOT key <@ int4range(6, end_key);
70+
tableoid | id1 | id2 | key | start_key | end_key
71+
--------------+-----+-----+-----+-----------+---------
72+
test.mytbl_6 | 1 | 1 | 5 | |
73+
(1 row)
74+
75+
/*
76+
* Test case by @dimarick
77+
*/
78+
CREATE TABLE test.parent (
79+
id SERIAL NOT NULL,
80+
owner_id INTEGER NOT NULL
81+
);
82+
CREATE TABLE test.child (
83+
parent_id INTEGER NOT NULL,
84+
owner_id INTEGER NOT NULL
85+
);
86+
CREATE TABLE test.child_nopart (
87+
parent_id INTEGER NOT NULL,
88+
owner_id INTEGER NOT NULL
89+
);
90+
INSERT INTO test.parent (owner_id) VALUES (1), (2), (3), (3);
91+
INSERT INTO test.child (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
92+
INSERT INTO test.child_nopart (parent_id, owner_id) VALUES (1, 1), (2, 2), (3, 3), (5, 3);
93+
SELECT pathman.create_hash_partitions('test.child', 'owner_id', 2);
94+
create_hash_partitions
95+
------------------------
96+
2
97+
(1 row)
98+
99+
/* gather statistics on test tables to have deterministic plans */
100+
ANALYZE;
101+
/* Query #1 */
102+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
103+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
104+
test.child.owner_id = test.parent.owner_id
105+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
106+
QUERY PLAN
107+
-----------------------------------------------------------------------------------------------------
108+
Nested Loop Left Join
109+
-> Seq Scan on parent
110+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
111+
-> Custom Scan (RuntimeAppend)
112+
Prune by: ((child.owner_id = 3) AND (child.owner_id = parent.owner_id))
113+
-> Seq Scan on child_1 child
114+
Filter: ((owner_id = 3) AND (owner_id = parent.owner_id) AND (parent_id = parent.id))
115+
(7 rows)
116+
117+
SELECT * FROM test.parent
118+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
119+
test.child.owner_id = test.parent.owner_id
120+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
121+
id | owner_id | parent_id | owner_id
122+
----+----------+-----------+----------
123+
3 | 3 | 3 | 3
124+
4 | 3 | |
125+
(2 rows)
126+
127+
/* Query #2 */
128+
EXPLAIN (COSTS OFF) SELECT * FROM test.parent
129+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
130+
test.child.owner_id = 3
131+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
132+
QUERY PLAN
133+
----------------------------------------------------------------------
134+
Nested Loop Left Join
135+
Join Filter: (child.parent_id = parent.id)
136+
-> Seq Scan on parent
137+
Filter: ((id = ANY ('{3,4}'::integer[])) AND (owner_id = 3))
138+
-> Seq Scan on child_1 child
139+
Filter: (owner_id = 3)
140+
(6 rows)
141+
142+
SELECT * FROM test.parent
143+
LEFT JOIN test.child ON test.child.parent_id = test.parent.id AND
144+
test.child.owner_id = 3
145+
WHERE test.parent.owner_id = 3 and test.parent.id IN (3, 4);
146+
id | owner_id | parent_id | owner_id
147+
----+----------+-----------+----------
148+
3 | 3 | 3 | 3
149+
4 | 3 | |
150+
(2 rows)
151+
152+
DROP TABLE test.child CASCADE;
153+
NOTICE: drop cascades to 2 other objects
154+
DROP TABLE test.child_nopart CASCADE;
155+
DROP TABLE test.mytbl CASCADE;
156+
NOTICE: drop cascades to 8 other objects
157+
DROP TABLE test.fk CASCADE;
158+
DROP TABLE test.parent CASCADE;
159+
DROP SCHEMA test;
160+
DROP EXTENSION pg_pathman CASCADE;
161+
DROP SCHEMA pathman;

‎expected/pathman_lateral_4.out

Lines changed: 128 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,128 @@
1+
/*
2+
* Sometimes join selectivity improvements patches in pgpro force nested loop
3+
* members swap -- in pathman_lateral_1.out and pathman_lateral_3.out
4+
*
5+
* Since 55a1954da16 and 6ef77cf46e8 (>= 13) output of EXPLAIN was changed,
6+
* now it includes aliases for inherited tables.
7+
*/
8+
\set VERBOSITY terse
9+
SET search_path = 'public';
10+
CREATE EXTENSION pg_pathman;
11+
CREATE SCHEMA test_lateral;
12+
/* create table partitioned by HASH */
13+
create table test_lateral.data(id int8 not null);
14+
select create_hash_partitions('test_lateral.data', 'id', 10);
15+
create_hash_partitions
16+
------------------------
17+
10
18+
(1 row)
19+
20+
insert into test_lateral.data select generate_series(1, 10000);
21+
VACUUM ANALYZE;
22+
set enable_hashjoin = off;
23+
set enable_mergejoin = off;
24+
/* all credits go to Ivan Frolkov */
25+
explain (costs off)
26+
select * from
27+
test_lateral.data as t1,
28+
lateral(select * from test_lateral.data as t2 where t2.id > t1.id) t2,
29+
lateral(select * from test_lateral.data as t3 where t3.id = t2.id + t1.id) t3
30+
where t1.id between 1 and 100 and
31+
t2.id between 2 and 299 and
32+
t1.id > t2.id and
33+
exists(select * from test_lateral.data t
34+
where t1.id = t2.id and t.id = t3.id);
35+
QUERY PLAN
36+
--------------------------------------------------------------------------------------------
37+
Nested Loop
38+
-> Nested Loop
39+
Join Filter: ((t2.id + t1.id) = t.id)
40+
-> HashAggregate
41+
Group Key: t.id
42+
-> Append
43+
-> Seq Scan on data_0 t_1
44+
-> Seq Scan on data_1 t_2
45+
-> Seq Scan on data_2 t_3
46+
-> Seq Scan on data_3 t_4
47+
-> Seq Scan on data_4 t_5
48+
-> Seq Scan on data_5 t_6
49+
-> Seq Scan on data_6 t_7
50+
-> Seq Scan on data_7 t_8
51+
-> Seq Scan on data_8 t_9
52+
-> Seq Scan on data_9 t_10
53+
-> Materialize
54+
-> Nested Loop
55+
Join Filter: ((t2.id > t1.id) AND (t1.id > t2.id) AND (t1.id = t2.id))
56+
-> Append
57+
-> Seq Scan on data_0 t2_1
58+
Filter: ((id >= 2) AND (id <= 299))
59+
-> Seq Scan on data_1 t2_2
60+
Filter: ((id >= 2) AND (id <= 299))
61+
-> Seq Scan on data_2 t2_3
62+
Filter: ((id >= 2) AND (id <= 299))
63+
-> Seq Scan on data_3 t2_4
64+
Filter: ((id >= 2) AND (id <= 299))
65+
-> Seq Scan on data_4 t2_5
66+
Filter: ((id >= 2) AND (id <= 299))
67+
-> Seq Scan on data_5 t2_6
68+
Filter: ((id >= 2) AND (id <= 299))
69+
-> Seq Scan on data_6 t2_7
70+
Filter: ((id >= 2) AND (id <= 299))
71+
-> Seq Scan on data_7 t2_8
72+
Filter: ((id >= 2) AND (id <= 299))
73+
-> Seq Scan on data_8 t2_9
74+
Filter: ((id >= 2) AND (id <= 299))
75+
-> Seq Scan on data_9 t2_10
76+
Filter: ((id >= 2) AND (id <= 299))
77+
-> Materialize
78+
-> Append
79+
-> Seq Scan on data_0 t1_1
80+
Filter: ((id >= 1) AND (id <= 100))
81+
-> Seq Scan on data_1 t1_2
82+
Filter: ((id >= 1) AND (id <= 100))
83+
-> Seq Scan on data_2 t1_3
84+
Filter: ((id >= 1) AND (id <= 100))
85+
-> Seq Scan on data_3 t1_4
86+
Filter: ((id >= 1) AND (id <= 100))
87+
-> Seq Scan on data_4 t1_5
88+
Filter: ((id >= 1) AND (id <= 100))
89+
-> Seq Scan on data_5 t1_6
90+
Filter: ((id >= 1) AND (id <= 100))
91+
-> Seq Scan on data_6 t1_7
92+
Filter: ((id >= 1) AND (id <= 100))
93+
-> Seq Scan on data_7 t1_8
94+
Filter: ((id >= 1) AND (id <= 100))
95+
-> Seq Scan on data_8 t1_9
96+
Filter: ((id >= 1) AND (id <= 100))
97+
-> Seq Scan on data_9 t1_10
98+
Filter: ((id >= 1) AND (id <= 100))
99+
-> Custom Scan (RuntimeAppend)
100+
Prune by: (t3.id = t.id)
101+
-> Seq Scan on data_0 t3
102+
Filter: (t.id = id)
103+
-> Seq Scan on data_1 t3
104+
Filter: (t.id = id)
105+
-> Seq Scan on data_2 t3
106+
Filter: (t.id = id)
107+
-> Seq Scan on data_3 t3
108+
Filter: (t.id = id)
109+
-> Seq Scan on data_4 t3
110+
Filter: (t.id = id)
111+
-> Seq Scan on data_5 t3
112+
Filter: (t.id = id)
113+
-> Seq Scan on data_6 t3
114+
Filter: (t.id = id)
115+
-> Seq Scan on data_7 t3
116+
Filter: (t.id = id)
117+
-> Seq Scan on data_8 t3
118+
Filter: (t.id = id)
119+
-> Seq Scan on data_9 t3
120+
Filter: (t.id = id)
121+
(84 rows)
122+
123+
set enable_hashjoin = on;
124+
set enable_mergejoin = on;
125+
DROP TABLE test_lateral.data CASCADE;
126+
NOTICE: drop cascades to 10 other objects
127+
DROP SCHEMA test_lateral;
128+
DROP EXTENSION pg_pathman;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp