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

Commitf5a405f

Browse files
committed
Fix the state:
AQO supports push-down of trivial JOIN and JOIN with mergejoinable clauses.
1 parent9c7230f commitf5a405f

File tree

2 files changed

+73
-13
lines changed

2 files changed

+73
-13
lines changed

‎expected/aqo_fdw.out‎

Lines changed: 48 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,9 @@ CREATE TABLE local (x int);
2121
CREATE FOREIGN TABLE frgn(x int) SERVER loopback OPTIONS (table_name 'local');
2222
INSERT INTO frgn (x) VALUES (1);
2323
ANALYZE local;
24-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
24+
-- Trivial foreign scan.s
25+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
26+
SELECT x FROM frgn;
2527
QUERY PLAN
2628
-------------------------------------------------------------
2729
Foreign Scan on frgn (actual rows=1 loops=1) (AQO not used)
@@ -30,7 +32,8 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
3032
JOINS: 0
3133
(4 rows)
3234

33-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
35+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
36+
SELECT x FROM frgn;
3437
QUERY PLAN
3538
-----------------------------------------------------------------------------
3639
Foreign Scan on frgn (actual rows=1 loops=1) (AQO: cardinality=1, error=0%)
@@ -39,8 +42,9 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn;
3942
JOINS: 0
4043
(4 rows)
4144

42-
-- Push down base filters.
43-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frgn WHERE x < 10;
45+
-- Push down base filters. Use verbose mode to see filters.
46+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
47+
SELECT x FROM frgn WHERE x < 10;
4448
QUERY PLAN
4549
--------------------------------------------------------------------
4650
Foreign Scan on public.frgn (actual rows=1 loops=1) (AQO not used)
@@ -51,7 +55,8 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frg
5155
JOINS: 0
5256
(6 rows)
5357

54-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frgn WHERE x < 10;
58+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
59+
SELECT x FROM frgn WHERE x < 10;
5560
QUERY PLAN
5661
------------------------------------------------------------------------------------
5762
Foreign Scan on public.frgn (actual rows=1 loops=1) (AQO: cardinality=1, error=0%)
@@ -62,7 +67,8 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE) SELECT x FROM frg
6267
JOINS: 0
6368
(6 rows)
6469

65-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
70+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
71+
SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
6672
QUERY PLAN
6773
-------------------------------------------------------------------------------
6874
Foreign Scan on frgn (actual rows=0 loops=1) (AQO: cardinality=1, error=100%)
@@ -71,4 +77,40 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) SELECT x FROM frgn WHERE x
7177
JOINS: 0
7278
(4 rows)
7379

80+
-- Trivial JOIN push-down.
81+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
82+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
83+
QUERY PLAN
84+
---------------------------------------------------------------------------
85+
Merge Join (actual rows=1 loops=1) (AQO not used)
86+
Merge Cond: (a.x = b.x)
87+
-> Sort (actual rows=1 loops=1) (AQO not used)
88+
Sort Key: a.x
89+
Sort Method: quicksort Memory: 25kB
90+
-> Foreign Scan on frgn a (actual rows=1 loops=1) (AQO not used)
91+
-> Sort (actual rows=1 loops=1) (AQO not used)
92+
Sort Key: b.x
93+
Sort Method: quicksort Memory: 25kB
94+
-> Foreign Scan on frgn b (actual rows=1 loops=1) (AQO not used)
95+
Using aqo: true
96+
AQO mode: LEARN
97+
JOINS: 0
98+
(13 rows)
99+
100+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
101+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
102+
QUERY PLAN
103+
---------------------------------------------------------------------
104+
Foreign Scan (actual rows=1 loops=1) (AQO: cardinality=1, error=0%)
105+
Relations: (frgn a) INNER JOIN (frgn b)
106+
Using aqo: true
107+
AQO mode: LEARN
108+
JOINS: 0
109+
(5 rows)
110+
111+
-- Non-mergejoinable join condition
112+
--EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
113+
--SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
114+
--EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
115+
--SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
74116
DROP EXTENSION aqo;

‎sql/aqo_fdw.sql‎

Lines changed: 25 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -26,13 +26,31 @@ CREATE FOREIGN TABLE frgn(x int) SERVER loopback OPTIONS (table_name 'local');
2626
INSERT INTO frgn (x)VALUES (1);
2727
ANALYZE local;
2828

29-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgn;
30-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgn;
31-
32-
-- Push down base filters.
33-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)SELECT xFROM frgnWHERE x<10;
34-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)SELECT xFROM frgnWHERE x<10;
35-
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)SELECT xFROM frgnWHERE x<-10;-- AQO ignores constants
29+
-- Trivial foreign scan.s
30+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
31+
SELECT xFROM frgn;
32+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
33+
SELECT xFROM frgn;
34+
35+
-- Push down base filters. Use verbose mode to see filters.
36+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
37+
SELECT xFROM frgnWHERE x<10;
38+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
39+
SELECT xFROM frgnWHERE x<10;
40+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
41+
SELECT xFROM frgnWHERE x<-10;-- AQO ignores constants
42+
43+
-- Trivial JOIN push-down.
44+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
45+
SELECT*FROM frgnAS a, frgnAS bWHEREa.x=b.x;
46+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
47+
SELECT*FROM frgnAS a, frgnAS bWHEREa.x=b.x;
48+
49+
-- TODO: Non-mergejoinable join condition.
50+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
51+
SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;
52+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
53+
SELECT*FROM frgnAS a, frgnAS bWHEREa.x<b.x;
3654

3755
DROP EXTENSION aqo;
3856

0 commit comments

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