10000 Bugfix. Filter system-dependent strings in an explain output of · postgrespro/aqo@d32b3d0 · GitHub
[go: up one dir, main page]
Skip to content

Commit d32b3d0

Browse files
danolivodanolivo
committed
Bugfix. Filter system-dependent strings in an explain output of
'aqo_fdw' and 'unsupported' regression tests.
1 parent 9d5ad30 commit d32b3d0

File tree

4 files changed

+47
-24
lines changed

4 files changed

+47
-24
lines changed

‎expected/aqo_fdw.out

Lines changed: 26 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,15 @@ 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-
-- Trivial foreign scan.s
24+
-- Utility tool. Allow to filter system-dependent strings from explain output.
25+
CREATE FUNCTION expln(query_string text) RETURNS SETOF text AS $$
26+
BEGIN
27+
RETURN QUERY
28+
EXECUTE format('%s', query_string);
29+
RETURN;
30+
END;
31+
$$ LANGUAGE PLPGSQL;
32+
-- Trivial foreign scan.
2533
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
2634
SELECT x FROM frgn;
2735
QUERY PLAN
@@ -75,28 +83,29 @@ SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
7583
(5 rows)
7684

7785
-- Trivial JOIN push-down.
78-
EXPLAIN (COSTS OFF)
79-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
80-
QUERY PLAN
81-
------------------------------------
82-
Merge Join
86+
SELECT str FROM expln('
87+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
88+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
89+
') AS str WHERE str NOT LIKE '%Sort Method%';
90+
str
91+
------------------------------------------------------------
92+
Merge Join (actual rows=1 loops=1)
93+
AQO not used
8394
Merge Cond: (a.x = b.x)
84-
-> Sort
95+
-> Sort (actual rows=1 loops=1)
96+
AQO not used
8597
Sort Key: a.x
86-
-> Foreign Scan on frgn a
87-
-> Sort
98+
-> Foreign Scan on frgn a (actual rows=1 loops=1)
99+
AQO not used
100+
-> Sort (actual rows=1 loops=1)
101+
AQO not used
88102
Sort Key: b.x
89-
-> Foreign Scan on frgn b
103+
-> Foreign Scan on frgn b (actual rows=1 loops=1)
104+
AQO not used
90105
Using aqo: true
91106
AQO mode: LEARN
92107
JOINS: 0
93-
(11 rows)
94-
95-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
96-
x | x
97-
---+---
98-
1 | 1
99-
(1 row)
108+
(16 rows)
100109

101110
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
102111
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;

‎expected/unsupported.out

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -3,22 +3,23 @@ SET aqo.mode = 'learn';
33
SET aqo.show_details = 'on';
44
DROP TABLE IF EXISTS t;
55
CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
6+
SELECT str FROM expln('
67
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
78
SELECT * FROM t GROUP BY (x) HAVING x > 3;
8-
QUERY PLAN
9+
') AS str WHERE str NOT LIKE '%Memory Usage%';
10+
str
911
-----------------------------------------------
1012
HashAggregate (actual rows=17 loops=1)
1113
AQO not used
1214
Group Key: x
13-
Batches: 1 Memory Usage: 40kB
1415
-> Seq Scan on t (actual rows=801 loops=1)
1516
AQO not used
1617
Filter: (x > 3)
1718
Rows Removed by Filter: 199
1819
Using aqo: true
1920
AQO mode: LEARN
2021
JOINS: 0
21-
(11 rows)
22+
(10 rows)
2223

2324
-- Do not support having clauses for now.
2425
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

‎sql/aqo_fdw.sql

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

29-
-- Trivial foreign scan.s
29+
-- Utility tool. Allow to filter system-dependent strings from explain output.
30+
CREATE FUNCTION expln(query_string text) RETURNS SETOF text AS $$
31+
BEGIN
32+
RETURN QUERY
33+
EXECUTE format('%s', query_string);
34+
RETURN;
35+
END;
36+
$$ LANGUAGE PLPGSQL;
37+
38+
-- Trivial foreign scan.
3039
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
3140
SELECT x FROM frgn;
3241
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
@@ -41,9 +50,11 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
4150
SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
4251

4352
-- Trivial JOIN push-down.
44-
EXPLAIN (COSTS OFF)
45-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
46-
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
53+
SELECT str FROM expln('
54+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
55+
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
56+
') AS str WHERE str NOT LIKE '%Sort Method%';
57+
4758
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
4859
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
4960

‎sql/unsupported.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,8 +5,10 @@ SET aqo.show_details = 'on';
55
DROP TABLE IF EXISTS t;
66
CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
77

8+
SELECT str FROM expln('
89
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
910
SELECT * FROM t GROUP BY (x) HAVING x > 3;
11+
') AS str WHERE str NOT LIKE '%Memory Usage%';
1012

1113
-- Do not support having clauses for now.
1214
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

0 commit comments

Comments
 (0)
0