8000 Add compute_query_id parameter in aqo configure with value as regress. · postgrespro/aqo@6ee752a · GitHub
[go: up one dir, main page]
Skip to content

Commit 6ee752a

Browse files
Alena RybakinaAlena0704
Alena Rybakina
authored and
Alena0704
committed
Add compute_query_id parameter in aqo configure with value as regress.
It is necessary for avoiding output Query Identifier while vanille's test are running. (Look at more in explain.c:612. We can get fail condition if only query identifier is not null) Where clause 'NOT LIKE '%Query Identifier%'' is throwed away due to being necessary any more. This addition parameter is appeared if we set compute_query_id parameter with value as 'auto'. Appearance of the parameter is checked in only gucs test.
1 parent c7fc679 commit 6ee752a

File tree

9 files changed

+42
-41
lines changed

9 files changed

+42
-41
lines changed

‎aqo.conf

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
autovacuum = off
22
shared_preload_libraries = 'postgres_fdw, aqo'
33
max_parallel_maintenance_workers = 1 # switch off parallel workers because of unsteadiness
4-
aqo.wide_search = 'on'
4+
aqo.wide_search = 'on'
5+
compute_query_id = 'regress'

‎expected/aqo_fdw.out

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -57,7 +57,7 @@ SELECT x FROM frgn;
5757
SELECT str FROM expln('
5858
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
5959
SELECT x FROM frgn WHERE x < 10;
60-
10000 ') AS str WHERE str NOT LIKE '%Query Identifier%';
60+
') AS str;
6161
str
6262
-----------------------------------------------------------
6363
Foreign Scan on public.frgn (actual rows=1 loops=1)
@@ -72,7 +72,7 @@ SELECT str FROM expln('
7272
SELECT str FROM expln('
7373
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
7474
SELECT x FROM frgn WHERE x < 10;
75-
') AS str WHERE str NOT LIKE '%Query Identifier%';
75+
') AS str;
7676
str
7777
-----------------------------------------------------------
7878
Foreign Scan on public.frgn (actual rows=1 loops=1)
@@ -114,7 +114,7 @@ SELECT str FROM expln('
114114
SELECT str FROM expln('
115115
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
116116
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
117-
') AS str WHERE str NOT LIKE '%Query Identifier%';
117+
') AS str;
118118
str
119119
--------------------------------------------------------------------------------------------------------
120120
Foreign Scan (actual rows=1 loops=1)
@@ -259,7 +259,7 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
259259
SELECT str FROM expln('
260260
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
261261
SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
262-
') AS str WHERE str NOT LIKE '%Query Identifier%';
262+
') AS str;
263263
str
264264
--------------------------------------------------------------------------------------------------------
265265
Foreign Scan (actual rows=0 loops=1)

‎expected/gucs.out

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,6 +10,7 @@ $$ LANGUAGE PLPGSQL;
1010
SET aqo.join_threshold = 0;
1111
SET aqo.mode = 'learn';
1212
SET aqo.show_details = true;
13+
SET compute_query_id = 'auto';
1314
CREATE TABLE t(x int);
1415
INSERT INTO t (x) (SELECT * FROM generate_series(1, 100) AS gs);
1516
ANALYZE t;
@@ -20,33 +21,37 @@ SELECT true FROM aqo_reset(); -- Remember! DROP EXTENSION doesn't remove any AQO
2021
(1 row)
2122

2223
-- Check AQO addons to explain (the only stable data)
23-
SELECT str FROM expln('
24+
SELECT regexp_replace(
25+
str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('
2426
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
2527
SELECT x FROM t;
26-
') AS str WHERE str NOT LIKE '%Query Identifier%';
28+
') AS str;
2729
str
2830
------------------------------------------------
2931
Seq Scan on public.t (actual rows=100 loops=1)
3032
AQO not used
3133
Output: x
34+
Query Identifier: N
3235
Using aqo: true
3336
AQO mode: LEARN
3437
JOINS: 0
35-
(6 rows)
38+
(7 rows)
3639

37-
SELECT str FROM expln('
40+
SELECT regexp_replace(
41+
str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('
3842
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
3943
SELECT x FROM t;
40-
') AS str WHERE str NOT LIKE '%Query Identifier%';
44+
') AS str;
4145
str
4246
------------------------------------------------
4347
Seq Scan on public.t (actual rows=100 loops=1)
4448
AQO: rows=100, error=0%
4549
Output: x
50+
Query Identifier: N
4651
Using aqo: true
4752
AQO mode: LEARN
4853
JOINS: 0
49-
(6 rows)
54+
(7 rows)
5055

5156
SET aqo.mode = 'disabled';
5257
-- Check existence of the interface functions.

‎expected/look_a_like.out

Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -25,8 +25,7 @@ $$ LANGUAGE PLPGSQL;
2525
-- in the next queries with the same fss_hash
2626
SELECT str AS result
2727
FROM expln('
28-
SELECT x FROM A where x = 5;') AS str
29-
WHERE str NOT LIKE 'Query Identifier%';
28+
SELECT x FROM A where x = 5;') AS str;
3029
result
3130
------------------------------------------------
3231
Seq Scan on public.a (actual rows=100 loops=1)
@@ -42,7 +41,6 @@ WHERE str NOT LIKE 'Query Identifier%';
4241
SELECT str AS result
4342
FROM expln('
4443
SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;') AS str
45-
WHERE str NOT LIKE 'Query Identifier%'
4644
; -- Find cardinality for SCAN A(x=5) from a neighbour class, created by the
4745
result
4846
--------------------------------------------------------
@@ -68,7 +66,6 @@ WHERE str NOT LIKE 'Query Identifier%'
6866
SELECT str AS result
6967
FROM expln('
7068
SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);') AS str
71-
WHERE str NOT LIKE 'Query Identifier%'
7269
; -- Find the JOIN cardinality from a neighbour class.
7370
result
7471
--------------------------------------------------------------
@@ -97,8 +94,7 @@ WHERE str NOT LIKE 'Query Identifier%'
9794
-- cardinality 100 in the first Seq Scan on a
9895
SELECT str AS result
9996
FROM expln('
100-
SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str
101-
WHERE str NOT LIKE 'Query Identifier%';
97+
SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str;
10298
result
10399
------------------------------------------------------
104100
GroupAggregate (actual rows=1 loops=1)
@@ -120,7 +116,7 @@ WHERE str NOT LIKE 'Query Identifier%';
120116
SELECT str AS result
121117
FROM expln('
122118
SELECT x FROM A where x < 10 group by(x);') AS str
123-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
119+
WHERE str NOT LIKE '%Memory%';
124120
result
125121
-------------------------------------------------------
126122
HashAggregate (actual rows=10 loops=1)
@@ -140,7 +136,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
140136
SELECT str AS result
141137
FROM expln('
142138
SELECT x,y FROM A,B WHERE x < 10 AND A.x = B.y;') AS str
143-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
139+
WHERE str NOT LIKE '%Memory%';
144140
result
145141
-------------------------------------------------------------
146142
Merge Join (actual rows=100000 loops=1)
@@ -169,7 +165,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
169165
SELECT str AS result
170166
FROM expln('
171167
SELECT x FROM A,B where x < 10 and y > 10 group by(x);') AS str
172-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
168+
WHERE str NOT LIKE '%Memory%';
173169
result
174170
----------------------------------------------------------
175171
HashAggregate (actual rows=0 loops=1)
@@ -200,7 +196,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
200196
SELECT str AS result
201197
FROM expln('
202198
SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;') AS str
203-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'
199+
WHERE str NOT LIKE '%Memory%'
204200
;
205201
result
206202
----------------------------------------------------------

‎expected/unsupported.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -530,7 +530,7 @@ SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
530530
SELECT str FROM expln('
531531
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF)
532532
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1') AS str
533-
WHERE str NOT LIKE '%Heap Blocks%' AND str NOT LIKE '%Query Identifier%';
533+
WHERE str NOT LIKE '%Heap Blocks%';
534534
str
535535
-----------------------------------------------------------------
536536
Aggregate (actual rows=1 loops=1)

‎sql/aqo_fdw.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -47,11 +47,11 @@ SELECT x FROM frgn;
4747
SELECT str FROM expln('
4848
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
4949
SELECT x FROM frgn WHERE x < 10;
50-
') AS str WHERE str NOT LIKE '%Query Identifier%';
50+
') AS str;
5151
SELECT str FROM expln('
5252
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
5353
SELECT x FROM frgn WHERE x < 10;
54-
') AS str WHERE str NOT LIKE '%Query Identifier%';
54+
') AS str;
5555
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
5656
SELECT x FROM frgn WHERE x < -10; -- AQO ignores constants
5757

@@ -65,7 +65,7 @@ SELECT str FROM expln('
6565
SELECT str FROM expln('
6666
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
6767
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
68-
') AS str WHERE str NOT LIKE '%Query Identifier%';
68+
') AS str;
6969

7070
CREATE TABLE local_a(aid int primary key, aval text);
7171
CREATE TABLE local_b(bid int primary key, aid int references local_a(aid), bval text);
@@ -133,7 +133,7 @@ SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
133133
SELECT str FROM expln('
134134
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF, VERBOSE)
135135
SELECT * FROM frgn AS a, frgn AS b WHERE a.x<b.x;
136-
') AS str WHERE str NOT LIKE '%Query Identifier%';
136+
') AS str;
137137

138138
DROP EXTENSION aqo CASCADE;
139139
DROP EXTENSION postgres_fdw CASCADE;

‎sql/gucs.sql

Lines changed: 7 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -12,21 +12,24 @@ $$ LANGUAGE PLPGSQL;
1212
SET aqo.join_threshold = 0;
1313
SET aqo.mode = 'learn';
1414
SET aqo.show_details = true;
15+
SET compute_query_id = 'auto';
1516

1617
CREATE TABLE t(x int);
1718
INSERT INTO t (x) (SELECT * FROM generate_series(1, 100) AS gs);
1819
ANALYZE t;
1920

2021
SELECT true FROM aqo_reset(); -- Remember! DROP EXTENSION doesn't remove any AQO data gathered.
2122
-- Check AQO addons to explain (the only stable data)
22-
SELECT str FROM expln('
23+
SELECT regexp_replace(
24+
str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('
2325
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
2426
SELECT x FROM t;
25-
') AS str WHERE str NOT LIKE '%Query Identifier%';
26-
SELECT str FROM expln('
27+
') AS str;
28+
SELECT regexp_replace(
29+
str,'Query Identifier: -?\m\d+\M','Query Identifier: N','g') as str FROM expln('
2730
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
2831
SELECT x FROM t;
29-
') AS str WHERE str NOT LIKE '%Query Identifier%';
32+
') AS str;
3033
SET aqo.mode = 'disabled';
3134

3235
-- Check existence of the interface functions.

‎sql/look_a_like.sql

Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -28,45 +28,41 @@ $$ LANGUAGE PLPGSQL;
2828
-- in the next queries with the same fss_hash
2929
SELECT str AS result
3030
FROM expln('
31-
SELECT x FROM A where x = 5;') AS str
32-
WHERE str NOT LIKE 'Query Identifier%';
31+
SELECT x FROM A where x = 5;') AS str;
3332

3433
SELECT str AS result
3534
FROM expln('
3635
SELECT x FROM A,B WHERE x = 5 AND A.x = B.y;') AS str
37-
WHERE str NOT LIKE 'Query Identifier%'
3836
; -- Find cardinality for SCAN A(x=5) from a neighbour class, created by the
3937
-- query, executed above.
4038

4139
SELECT str AS result
4240
FROM expln('
4341
SELECT x, sum(x) FROM A,B WHERE y = 5 AND A.x = B.y group by(x);') AS str
44-
WHERE str NOT LIKE 'Query Identifier%'
4542
; -- Find the JOIN cardinality from a neighbour class.
4643

4744
-- cardinality 100 in the first Seq Scan on a
4845
SELECT str AS result
4946
FROM expln('
50-
SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str
51-
WHERE str NOT LIKE 'Query Identifier%';
47+
SELECT x, sum(x) FROM A WHERE x = 5 group by(x);') AS str;
5248

5349
-- no one predicted rows. we use knowledge cardinalities of the query
5450
-- in the next queries with the same fss_hash
5551
SELECT str AS result
5652
FROM expln('
5753
SELECT x FROM A where x < 10 group by(x);') AS str
58-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
54+
WHERE str NOT LIKE '%Memory%';
5955
-- cardinality 1000 in Seq Scan on a
6056
SELECT str AS result
6157
FROM expln('
6258
SELECT x,y FROM A,B WHERE x < 10 AND A.x = B.y;') AS str
63-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
59+
WHERE str NOT LIKE '%Memory%';
6460

6561
-- cardinality 100 in Seq Scan on a and Seq Scan on b
6662
SELECT str AS result
6763
FROM expln('
6864
SELECT x FROM A,B where x < 10 and y > 10 group by(x);') AS str
69-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
65+
WHERE str NOT LIKE '%Memory%';
7066

7167
--
7268
-- TODO:
@@ -75,7 +71,7 @@ WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%';
7571
SELECT str AS result
7672
FROM expln('
7773
SELECT x,y FROM A,B WHERE x < 10 and y > 10 AND A.x = B.y;') AS str
78-
WHERE str NOT LIKE 'Query Identifier%' and str NOT LIKE '%Memory%'
74+
WHERE str NOT LIKE '%Memory%'
7975
;
8076

8177
RESET enable_material;

‎sql/unsupported.sql

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -165,7 +165,7 @@ SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
165165
SELECT str FROM expln('
166166
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, SUMMARY OFF, TIMING OFF)
167167
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1') AS str
168-
WHERE str NOT LIKE '%Heap Blocks%' AND str NOT LIKE '%Query Identifier%';
168+
WHERE str NOT LIKE '%Heap Blocks%';
169169

170170
-- Best choice is ...
171171
ANALYZE t;

0 commit comments

Comments
 (0)
0