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

Commit6c30c32

Browse files
committed
Merge branch 'rel_future_beta' into rel_future_expressions
2 parents4dd25a9 +77d4164 commit6c30c32

22 files changed

+756
-271
lines changed

‎Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@ REGRESS = pathman_basic \
3535
pathman_inserts\
3636
pathman_interval\
3737
pathman_join_clause\
38+
pathman_lateral\
3839
pathman_only\
3940
pathman_permissions\
4041
pathman_rowmarks\

‎README.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -68,7 +68,7 @@ Modify the **`shared_preload_libraries`** parameter in `postgresql.conf` as foll
6868
```
6969
shared_preload_libraries = 'pg_pathman'
7070
```
71-
>**Important:**`pg_pathman` mayhave conflicts with some other extensionswhich uses the same hook functions. For example,`pg_pathman` uses`ProcessUtility_hook`hookto handle COPY queries for partitioned tables. Anditcould sometimesinterfere with`pg_stat_statements`extension which uses the same hook. In this case tryto listlibraries in certain order:`shared_preload_libraries = 'pg_pathman, pg_stat_statements'`
71+
>**Important:**`pg_pathman` maycause conflicts with some other extensionsthat use the same hook functions. For example,`pg_pathman` uses`ProcessUtility_hook` to handle COPY queries for partitioned tables, which meansitmayinterfere with`pg_stat_statements`from time to time. In this case, trylistinglibraries in certain order:`shared_preload_libraries = 'pg_stat_statements, pg_pathman'`.
7272
7373
It is essential to restart the PostgreSQL instance. After that, execute the following query in psql:
7474
```plpgsql

‎expected/pathman_join_clause.out

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -6,40 +6,41 @@ CREATE SCHEMA test;
66
* Test push down a join clause into child nodes of append
77
*/
88
/* create test tables */
9-
CREATE TABLE fk (
9+
CREATE TABLEtest.fk (
1010
id1 INT NOT NULL,
1111
id2 INT NOT NULL,
1212
start_key INT,
1313
end_key INT,
1414
PRIMARY KEY (id1, id2));
15-
CREATE TABLE mytbl (
15+
CREATE TABLEtest.mytbl (
1616
id1 INT NOT NULL,
1717
id2 INT NOT NULL,
1818
key INT NOT NULL,
19-
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCES fk(id1, id2),
19+
CONSTRAINT fk_fk FOREIGN KEY (id1, id2) REFERENCEStest.fk(id1, id2),
2020
PRIMARY KEY (id1, key));
21-
SELECT pathman.create_hash_partitions('mytbl', 'id1', 8);
21+
SELECT pathman.create_hash_partitions('test.mytbl', 'id1', 8);
2222
create_hash_partitions
2323
------------------------
2424
8
2525
(1 row)
2626

2727
/* ...fill out with test data */
28-
INSERT INTO fk VALUES (1, 1);
29-
INSERT INTO mytbl VALUES (1, 1, 5), (1,1,6);
28+
INSERT INTOtest.fk VALUES (1, 1);
29+
INSERT INTOtest.mytbl VALUES (1, 1, 5), (1,1,6);
3030
/* gather statistics on test tables to have deterministic plans */
31-
ANALYZE fk;
32-
ANALYZE mytbl;
31+
ANALYZEtest.fk;
32+
ANALYZEtest.mytbl;
3333
/* run test queries */
3434
EXPLAIN (COSTS OFF) /* test plan */
3535
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
36-
FROM mytbl m JOIN fk USING(id1, id2)
36+
FROMtest.mytbl m JOINtest.fk USING(id1, id2)
3737
WHERE NOT key <@ int4range(6, end_key);
3838
QUERY PLAN
3939
------------------------------------------------------------------------------------
4040
Nested Loop
4141
-> Seq Scan on fk
4242
-> Custom Scan (RuntimeAppend)
43+
Prune by: (fk.id1 = m.id1)
4344
-> Bitmap Heap Scan on mytbl_0 m
4445
Recheck Cond: (id1 = fk.id1)
4546
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
@@ -80,18 +81,18 @@ SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
8081
Filter: ((fk.id2 = id2) AND (NOT (key <@ int4range(6, fk.end_key))))
8182
-> Bitmap Index Scan on mytbl_7_pkey
8283
Index Cond: (id1 = fk.id1)
83-
(43 rows)
84+
(44 rows)
8485

8586
/* test joint data */
8687
SELECT m.tableoid::regclass, id1, id2, key, start_key, end_key
87-
FROM mytbl m JOIN fk USING(id1, id2)
88+
FROMtest.mytbl m JOINtest.fk USING(id1, id2)
8889
WHERE NOT key <@ int4range(6, end_key);
89-
tableoid | id1 | id2 | key | start_key | end_key
90-
----------+-----+-----+-----+-----------+---------
91-
mytbl_6 | 1 | 1 | 5 | |
90+
tableoid | id1 | id2 | key | start_key | end_key
91+
--------------+-----+-----+-----+-----------+---------
92+
test.mytbl_6 | 1 | 1 | 5 | |
9293
(1 row)
9394

9495
DROP SCHEMA test CASCADE;
96+
NOTICE: drop cascades to 10 other objects
9597
DROP EXTENSION pg_pathman CASCADE;
96-
NOTICE: drop cascades to 8 other objects
9798
DROP SCHEMA pathman CASCADE;

‎expected/pathman_lateral.out

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
\set VERBOSITY terse
2+
SET search_path = 'public';
3+
CREATE EXTENSION pg_pathman;
4+
CREATE SCHEMA test_lateral;
5+
/* create table partitioned by HASH */
6+
create table test_lateral.data(id int8 not null);
7+
select create_hash_partitions('test_lateral.data', 'id', 10);
8+
create_hash_partitions
9+
------------------------
10+
10
11+
(1 row)
12+
13+
insert into test_lateral.data select generate_series(1, 10000);
14+
VACUUM ANALYZE;
15+
set enable_hashjoin = off;
16+
set enable_mergejoin = off;
17+
/* all credits go to Ivan Frolkov */
18+
explain (costs off)
19+
select * from
20+
test_lateral.data as t1,
21+
lateral(select * from test_lateral.data as t2 where t2.id > t1.id) t2,
22+
lateral(select * from test_lateral.data as t3 where t3.id = t2.id + t1.id) t3
23+
where t1.id between 1 and 100 and
24+
t2.id between 2 and 299 and
25+
t1.id > t2.id and
26+
exists(select * from test_lateral.data t
27+
where t1.id = t2.id and t.id = t3.id);
28+
QUERY PLAN
29+
--------------------------------------------------------------------------------------------
30+
Nested Loop Semi Join
31+
-> Nested Loop
32+
Join Filter: ((t2.id + t1.id) = t3.id)
33+
-> Append
34+
-> Seq Scan on data_0 t3
35+
-> Seq Scan on data_1 t3_1
36+
-> Seq Scan on data_2 t3_2
37+
-> Seq Scan on data_3 t3_3
38+
-> Seq Scan on data_4 t3_4
39+
-> Seq Scan on data_5 t3_5
40+
-> Seq Scan on data_6 t3_6
41+
-> Seq Scan on data_7 t3_7
42+
-> Seq Scan on data_8 t3_8
43+
-> Seq Scan on data_9 t3_9
44+
-> Materialize
45+
-> Nested Loop
46+
Join Filter: ((t2.id > t1.id) AND (t1.id > t2.id) AND (t1.id = t2.id))
47+
-> Append
48+
-> Seq Scan on data_0 t2
49+
Filter: ((id >= 2) AND (id <= 299))
50+
-> Seq Scan on data_1 t2_1
51+
Filter: ((id >= 2) AND (id <= 299))
52+
-> Seq Scan on data_2 t2_2
53+
Filter: ((id >= 2) AND (id <= 299))
54+
-> Seq Scan on data_3 t2_3
55+
Filter: ((id >= 2) AND (id <= 299))
56+
-> Seq Scan on data_4 t2_4
57+
Filter: ((id >= 2) AND (id <= 299))
58+
-> Seq Scan on data_5 t2_5
59+
Filter: ((id >= 2) AND (id <= 299))
60+
-> Seq Scan on data_6 t2_6
61+
Filter: ((id >= 2) AND (id <= 299))
62+
-> Seq Scan on data_7 t2_7
63+
Filter: ((id >= 2) AND (id <= 299))
64+
-> Seq Scan on data_8 t2_8
65+
Filter: ((id >= 2) AND (id <= 299))
66+
-> Seq Scan on data_9 t2_9
67+
Filter: ((id >= 2) AND (id <= 299))
68+
-> Materialize
69+
-> Append
70+
-> Seq Scan on data_0 t1
71+
Filter: ((id >= 1) AND (id <= 100))
72+
-> Seq Scan on data_1 t1_1
73+
Filter: ((id >= 1) AND (id <= 100))
74+
-> Seq Scan on data_2 t1_2
75+
Filter: ((id >= 1) AND (id <= 100))
76+
-> Seq Scan on data_3 t1_3
77+
Filter: ((id >= 1) AND (id <= 100))
78+
-> Seq Scan on data_4 t1_4
79+
Filter: ((id >= 1) AND (id <= 100))
80+
-> Seq Scan on data_5 t1_5
81+
Filter: ((id >= 1) AND (id <= 100))
82+
-> Seq Scan on data_6 t1_6
83+
Filter: ((id >= 1) AND (id <= 100))
84+
-> Seq Scan on data_7 t1_7
85+
Filter: ((id >= 1) AND (id <= 100))
86+
-> Seq Scan on data_8 t1_8
87+
Filter: ((id >= 1) AND (id <= 100))
88+
-> Seq Scan on data_9 t1_9
89+
Filter: ((id >= 1) AND (id <= 100))
90+
-> Custom Scan (RuntimeAppend)
91+
Prune by: (t3.id = t.id)
92+
-> Seq Scan on data_0 t
93+
Filter: (t3.id = id)
94+
-> Seq Scan on data_1 t
95+
Filter: (t3.id = id)
96+
-> Seq Scan on data_2 t
97+
Filter: (t3.id = id)
98+
-> Seq Scan on data_3 t
99+
Filter: (t3.id = id)
100+
-> Seq Scan on data_4 t
101+
Filter: (t3.id = id)
102+
-> Seq Scan on data_5 t
103+
Filter: (t3.id = id)
104+
-> Seq Scan on data_6 t
105+
Filter: (t3.id = id)
106+
-> Seq Scan on data_7 t
107+
Filter: (t3.id = id)
108+
-> Seq Scan on data_8 t
109+
Filter: (t3.id = id)
110+
-> Seq Scan on data_9 t
111+
Filter: (t3.id = id)
112+
(82 rows)
113+
114+
set enable_hashjoin = on;
115+
set enable_mergejoin = on;
116+
DROP SCHEMA test_lateral CASCADE;
117+
NOTICE: drop cascades to 11 other objects
118+
DROP EXTENSION pg_pathman;

‎expected/pathman_only.out

Lines changed: 14 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -178,17 +178,28 @@ SELECT * FROM test_only.from_only_test JOIN q1 USING(val);
178178
-> Seq Scan on from_only_test from_only_test_1
179179
-> CTE Scan on q1
180180
-> Custom Scan (RuntimeAppend)
181+
Prune by: (q1.val = from_only_test.val)
181182
-> Seq Scan on from_only_test_1 from_only_test
183+
Filter: (q1.val = val)
182184
-> Seq Scan on from_only_test_2 from_only_test
185+
Filter: (q1.val = val)
183186
-> Seq Scan on from_only_test_3 from_only_test
187+
Filter: (q1.val = val)
184188
-> Seq Scan on from_only_test_4 from_only_test
189+
Filter: (q1.val = val)
185190
-> Seq Scan on from_only_test_5 from_only_test
191+
Filter: (q1.val = val)
186192
-> Seq Scan on from_only_test_6 from_only_test
193+
Filter: (q1.val = val)
187194
-> Seq Scan on from_only_test_7 from_only_test
195+
Filter: (q1.val = val)
188196
-> Seq Scan on from_only_test_8 from_only_test
197+
Filter: (q1.val = val)
189198
-> Seq Scan on from_only_test_9 from_only_test
199+
Filter: (q1.val = val)
190200
-> Seq Scan on from_only_test_10 from_only_test
191-
(15 rows)
201+
Filter: (q1.val = val)
202+
(26 rows)
192203

193204
/* should be OK */
194205
EXPLAIN (COSTS OFF)
@@ -199,6 +210,7 @@ WHERE val = (SELECT val FROM ONLY test_only.from_only_test
199210
QUERY PLAN
200211
-----------------------------------------------------------------
201212
Custom Scan (RuntimeAppend)
213+
Prune by: (from_only_test.val = $0)
202214
InitPlan 1 (returns $0)
203215
-> Limit
204216
-> Sort
@@ -224,7 +236,7 @@ WHERE val = (SELECT val FROM ONLY test_only.from_only_test
224236
Filter: (val = $0)
225237
-> Seq Scan on from_only_test_10 from_only_test
226238
Filter: (val = $0)
227-
(26 rows)
239+
(27 rows)
228240

229241
DROP SCHEMA test_only CASCADE;
230242
NOTICE: drop cascades to 12 other objects

‎expected/pathman_rowmarks.out

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -100,6 +100,7 @@ FOR SHARE;
100100
-> Seq Scan on first_3
101101
-> Seq Scan on first_4
102102
-> Custom Scan (RuntimeAppend)
103+
Prune by: (first.id = $1)
103104
-> Seq Scan on first_0 first
104105
Filter: (id = $1)
105106
-> Seq Scan on first_1 first
@@ -110,7 +111,7 @@ FOR SHARE;
110111
Filter: (id = $1)
111112
-> Seq Scan on first_4 first
112113
Filter: (id = $1)
113-
(23 rows)
114+
(24 rows)
114115

115116
/* A little harder (execution) */
116117
SELECT * FROM rowmarks.first
@@ -142,6 +143,7 @@ FOR SHARE;
142143
Sort Key: second.id
143144
-> Seq Scan on second
144145
-> Custom Scan (RuntimeAppend)
146+
Prune by: (first.id = $1)
145147
-> Seq Scan on first_0 first
146148
Filter: (id = $1)
147149
-> Seq Scan on first_1 first
@@ -152,7 +154,7 @@ FOR SHARE;
152154
Filter: (id = $1)
153155
-> Seq Scan on first_4 first
154156
Filter: (id = $1)
155-
(18 rows)
157+
(19 rows)
156158

157159
/* Two tables (execution) */
158160
SELECT * FROM rowmarks.first

0 commit comments

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