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

Commit 688c475

Browse files
danolivodanolivo
committed
Bugfix. Filter system-dependent strings in an explain output for
'aqo_fdw', 'unsupported' regression tests.
1 parent 8db8f49 commit 688c475

File tree

4 files changed

+31
-9
lines changed

4 files changed

+31
-9
lines changed

‎expected/aqo_fdw.out

Lines changed: 13 additions & 5 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,29 +83,29 @@ SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
7583
(5 rows)
7684

7785
-- Trivial JOIN push-down.
86+
SELECT str FROM expln('
7887
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
7988
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
80-
QUERY PLAN
89+
') AS str WHERE str NOT LIKE '%Sort Method%';
90+
str
8191
------------------------------------------------------------
8292
Merge Join (actual rows=1 loops=1)
8393
AQO not used
8494
Merge Cond: (a.x = b.x)
8595
-> Sort (actual rows=1 loops=1)
8696
AQO not used
8797
Sort Key: a.x
88-
Sort Method: quicksort Memory: 25kB
8998
-> Foreign Scan on frgn a (actual rows=1 loops=1)
9099
AQO not used
91100
-> Sort (actual rows=1 loops=1)
92101
AQO not used
93102
Sort Key: b.x
94-
Sort Method: quicksort Memory: 25kB
95103
-> Foreign Scan on frgn b (actual rows=1 loops=1)
96104
AQO not used
97105
Using aqo: true
98106
AQO mode: LEARN
99107
JOINS: 0
100-
(18 rows)
108+
(16 rows)
101109

102110
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
103111
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
@@ -322,13 +322,15 @@ SELECT count(*) FROM
322322
42550
323323
(1 row)
324324

325+
SELECT str FROM expln('
325326
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
326327
SELECT count(*) FROM
327328
(SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1
328329
JOIN
329330
(SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2
330331
ON q1.x = q2.x+1;
331-
QUERY PLAN
332+
') AS str WHERE str NOT LIKE '%Memory Usage%';
333+
str
332334
------------------------------------------------------------------------------
333335
Aggregate (actual rows=1 loops=1)
334336
AQO not used
@@ -347,7 +349,6 @@ SELECT count(*) FROM
347349
Rows Removed by Filter: 50
348350
-> Hash (actual rows=851 loops=1)
349351
AQO not used
350-
Buckets: 1024 Batches: 1 Memory Usage: 38kB
351352
-> Seq Scan on t (actual rows=851 loops=1)
352353
AQO: rows=851, error=0%
353354
Filter: (((x % 3))::numeric < (SubPlan 1))
@@ -362,7 +363,7 @@ SELECT count(*) FROM
362363
Using aqo: true
363364
AQO mode: LEARN
364365
JOINS: 1
365-
(32 rows)
366+
(31 rows)
366367

367368
-- Two identical subplans in a clause
368369
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

‎sql/aqo_fdw.sql

Lines changed: 12 additions & 1 deletion
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,8 +50,10 @@ 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.
53+
SELECT str FROM expln('
4454
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
4555
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
56+
') AS str WHERE str NOT LIKE '%Sort Method%';
4657
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
4758
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
4859

‎sql/unsupported.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,12 +94,14 @@ SELECT count(*) FROM
9494
JOIN
9595
(SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2
9696
ON q1.x = q2.x+1;
97+
SELECT str FROM expln('
9798
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
9899
SELECT count(*) FROM
99100
(SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1
100101
JOIN
101102
(SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2
102103
ON q1.x = q2.x+1;
104+
') AS str WHERE str NOT LIKE '%Memory Usage%';
103105

104106
-- Two identical subplans in a clause
105107
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)

0 commit comments

Comments
 (0)
0