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

Commitd640ff1

Browse files
committed
test sophisticated queries with lateral (by Ivan Frolkov)
1 parentcb7658e commitd640ff1

File tree

3 files changed

+159
-0
lines changed

3 files changed

+159
-0
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\

‎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;

‎sql/pathman_lateral.sql

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
\set VERBOSITY terse
2+
3+
SET search_path='public';
4+
CREATE EXTENSION pg_pathman;
5+
CREATESCHEMAtest_lateral;
6+
7+
8+
/* create table partitioned by HASH*/
9+
createtabletest_lateral.data(id int8not null);
10+
select create_hash_partitions('test_lateral.data','id',10);
11+
insert intotest_lateral.dataselect generate_series(1,10000);
12+
13+
14+
VACUUM ANALYZE;
15+
16+
17+
set enable_hashjoin= off;
18+
set enable_mergejoin= off;
19+
20+
21+
/* all credits go to Ivan Frolkov*/
22+
explain (costs off)
23+
select*from
24+
test_lateral.dataas t1,
25+
lateral(select*fromtest_lateral.dataas t2wheret2.id>t1.id) t2,
26+
lateral(select*fromtest_lateral.dataas t3wheret3.id=t2.id+t1.id) t3
27+
wheret1.id between1and100and
28+
t2.id between2and299and
29+
t1.id>t2.idand
30+
exists(select*fromtest_lateral.data t
31+
wheret1.id=t2.idandt.id=t3.id);
32+
33+
34+
set enable_hashjoin=on;
35+
set enable_mergejoin=on;
36+
37+
38+
39+
DROPSCHEMA test_lateral CASCADE;
40+
DROP EXTENSION pg_pathman;

0 commit comments

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