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

Commit49f9947

Browse files
pyhalovdanolivo
authored andcommitted
[PGPRO-7183] bring in line stable 13, 14, 15
Cherry-pick commit:b3bb11fAdd tests on partitioned tables with foreign partitions.
1 parentdcf7c0e commit49f9947

File tree

2 files changed

+126
-1
lines changed

2 files changed

+126
-1
lines changed

‎expected/aqo_fdw.out

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -146,6 +146,89 @@ WHERE a.aid = b.aid AND b.bval like 'val%';
146146
JOINS: 0
147147
(6 rows)
148148

149+
-- Partitioned join over foreign tables
150+
set enable_partitionwise_join = on;
151+
ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost '1.0');
152+
CREATE TABLE local_main_p0(aid int, aval text);
153+
CREATE TABLE local_main_p1(aid int, aval text);
154+
CREATE TABLE main (aid int, aval text) PARTITION BY HASH(aid);
155+
CREATE FOREIGN TABLE main_p0 PARTITION OF main FOR VALUES WITH (MODULUS 3, REMAINDER 0)
156+
SERVER loopback OPTIONS (table_name 'local_main_p0');
157+
CREATE FOREIGN TABLE main_p1 PARTITION OF main FOR VALUES WITH (MODULUS 3, REMAINDER 1)
158+
SERVER loopback OPTIONS (table_name 'local_main_p1');
159+
CREATE TABLE main_p2 PARTITION OF main FOR VALUES WITH (MODULUS 3, REMAINDER 2);
160+
CREATE TABLE local_ref_p0(bid int, aid int, bval text);
161+
CREATE TABLE local_ref_p1(bid int, aid int, bval text);
162+
CREATE TABLE ref (bid int, aid int, bval text) PARTITION BY HASH(aid);
163+
CREATE FOREIGN TABLE ref_p0 PARTITION OF ref FOR VALUES WITH (MODULUS 3, REMAINDER 0)
164+
SERVER loopback OPTIONS (table_name 'local_ref_p0');
165+
CREATE FOREIGN TABLE ref_p1 PARTITION OF ref FOR VALUES WITH (MODULUS 3, REMAINDER 1)
166+
SERVER loopback OPTIONS (table_name 'local_ref_p1');
167+
CREATE TABLE ref_p2 PARTITION OF ref FOR VALUES WITH (MODULUS 3, REMAINDER 2);
168+
INSERT INTO main SELECT i, 'val_' || i FROM generate_series(1,100) i;
169+
INSERT INTO ref SELECT i, mod(i, 10) + 1, 'val_' || i FROM generate_series(1,1000) i;
170+
ANALYZE local_main_p0, local_main_p1, main_p2;
171+
ANALYZE local_ref_p0, local_ref_p1, ref_p2;
172+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
173+
SELECT * from main AS a, ref AS b
174+
WHERE a.aid = b.aid AND b.bval like 'val%';
175+
QUERY PLAN
176+
--------------------------------------------------------------------
177+
Append (actual rows=1000 loops=1)
178+
AQO not used
179+
-> Foreign Scan (actual rows=400 loops=1)
180+
AQO not used
181+
Relations: (main_p0 a_1) INNER JOIN (ref_p0 b_1)
182+
-> Foreign Scan (actual rows=300 loops=1)
183+
AQO not used
184+
Relations: (main_p1 a_2) INNER JOIN (ref_p1 b_2)
185+
-> Hash Join (actual rows=300 loops=1)
186+
AQO not used
187+
Hash Cond: (b_3.aid = a_3.aid)
188+
-> Seq Scan on ref_p2 b_3 (actual rows=300 loops=1)
189+
AQO not used
190+
Filter: (bval ~~ 'val%'::text)
191+
-> Hash (actual rows=38 loops=1)
192+
Buckets: 1024 Batches: 1 Memory Usage: 10kB
193+
-> Seq Scan on main_p2 a_3 (actual rows=38 loops=1)
194+
AQO not used
195+
Using aqo: true
196+
AQO mode: LEARN
197+
JOINS: 1
198+
(21 rows)
199+
200+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
201+
SELECT * from main AS a, ref AS b
202+
WHERE a.aid = b.aid AND b.bval like 'val%';
203+
QUERY PLAN
204+
--------------------------------------------------------------------
205+
Append (actual rows=1000 loops=1)
206+
AQO not used
207+
-> Foreign Scan (actual rows=400 loops=1)
208+
AQO: rows=400, error=0%
209+
Relations: (main_p0 a_1) INNER JOIN (ref_p0 b_1)
210+
-> Foreign Scan (actual rows=300 loops=1)
211+
AQO: rows=300, error=0%
212+
Relations: (main_p1 a_2) INNER JOIN (ref_p1 b_2)
213+
-> Hash Join (actual rows=300 loops=1)
214+
AQO: rows=300, error=0%
215+
Hash Cond: (b_3.aid = a_3.aid)
216+
-> Seq Scan on ref_p2 b_3 (actual rows=300 loops=1)
217+
AQO: rows=300, error=0%
218+
Filter: (bval ~~ 'val%'::text)
219+
-> Hash (actual rows=38 loops=1)
220+
Buckets: 1024 Batches: 1 Memory Usage: 10kB
221+
-> Seq Scan on main_p2 a_3 (actual rows=38 loops=1)
222+
AQO: rows=38, error=0%
223+
Using aqo: true
224+
AQO mode: LEARN
225+
JOINS: 1
226+
(21 rows)
227+
228+
DROP TABLE main, local_main_p0, local_main_p1;
229+
DROP TABLE ref, local_ref_p0, local_ref_p1;
230+
ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
231+
reset enable_partitionwise_join;
149232
-- TODO: Non-mergejoinable join condition.
150233
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
151234
SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;

‎sql/aqo_fdw.sql

Lines changed: 43 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,49 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
7777
SELECT*from frgn_aAS a, frgn_bAS b
7878
WHEREa.aid=b.aidANDb.bvallike'val%';
7979

80+
-- Partitioned join over foreign tables
81+
set enable_partitionwise_join=on;
82+
ALTER SERVER loopback OPTIONS (ADD fdw_tuple_cost'1.0');
83+
84+
CREATETABLElocal_main_p0(aidint, avaltext);
85+
CREATETABLElocal_main_p1(aidint, avaltext);
86+
CREATETABLEmain (aidint, avaltext) PARTITION BY HASH(aid);
87+
88+
CREATE FOREIGN TABLE main_p0 PARTITION OF main FORVALUES WITH (MODULUS3, REMAINDER0)
89+
SERVER loopback OPTIONS (table_name'local_main_p0');
90+
CREATE FOREIGN TABLE main_p1 PARTITION OF main FORVALUES WITH (MODULUS3, REMAINDER1)
91+
SERVER loopback OPTIONS (table_name'local_main_p1');
92+
CREATETABLEmain_p2 PARTITION OF main FORVALUES WITH (MODULUS3, REMAINDER2);
93+
94+
CREATETABLElocal_ref_p0(bidint, aidint, bvaltext);
95+
CREATETABLElocal_ref_p1(bidint, aidint, bvaltext);
96+
CREATETABLEref (bidint, aidint, bvaltext) PARTITION BY HASH(aid);
97+
98+
CREATE FOREIGN TABLE ref_p0 PARTITION OF ref FORVALUES WITH (MODULUS3, REMAINDER0)
99+
SERVER loopback OPTIONS (table_name'local_ref_p0');
100+
CREATE FOREIGN TABLE ref_p1 PARTITION OF ref FORVALUES WITH (MODULUS3, REMAINDER1)
101+
SERVER loopback OPTIONS (table_name'local_ref_p1');
102+
CREATETABLEref_p2 PARTITION OF ref FORVALUES WITH (MODULUS3, REMAINDER2);
103+
104+
INSERT INTO mainSELECT i,'val_'|| iFROM generate_series(1,100) i;
105+
INSERT INTO refSELECT i, mod(i,10)+1,'val_'|| iFROM generate_series(1,1000) i;
106+
107+
ANALYZE local_main_p0, local_main_p1, main_p2;
108+
ANALYZE local_ref_p0, local_ref_p1, ref_p2;
109+
110+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
111+
SELECT*from mainAS a, refAS b
112+
WHEREa.aid=b.aidANDb.bvallike'val%';
113+
114+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
115+
SELECT*from mainAS a, refAS b
116+
WHEREa.aid=b.aidANDb.bvallike'val%';
117+
118+
DROPTABLE main, local_main_p0, local_main_p1;
119+
DROPTABLE ref, local_ref_p0, local_ref_p1;
120+
ALTER SERVER loopback OPTIONS (DROP fdw_tuple_cost);
121+
reset enable_partitionwise_join;
122+
80123
-- TODO: Non-mergejoinable join condition.
81124
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
82125
SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;
@@ -88,4 +131,3 @@ DROP EXTENSION postgres_fdw CASCADE;
88131
DROPTABLE local;
89132
DROPTABLE local_b;
90133
DROPTABLE local_a;
91-

0 commit comments

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