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

Commit30d0706

Browse files
committed
Create lateral test output file for pgpro.
1 parent44b8962 commit30d0706

File tree

3 files changed

+126
-0
lines changed

3 files changed

+126
-0
lines changed

‎expected/pathman_lateral.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,5 @@
1+
-- Sometimes join selectivity improvements patches in pgpro force nested loop
2+
-- members swap -- in pathman_lateral_1.out
13
\set VERBOSITY terse
24
SET search_path = 'public';
35
CREATE EXTENSION pg_pathman;

‎expected/pathman_lateral_1.out

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

‎sql/pathman_lateral.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,6 @@
1+
-- Sometimes join selectivity improvements patches in pgpro force nested loop
2+
-- members swap -- in pathman_lateral_1.out
3+
14
\set VERBOSITY terse
25

36
SET search_path='public';

0 commit comments

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