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

Commited0b103

Browse files
committed
One more step towards improving the AQO regression tests stability.
Move GUCs, which can be changed in runtime, from global regression tests confto first executed test 'aqo_disabled.sql'. There we set these values by ALTERSYSTEM/pg_reload_conf() and use them during the test.Also, we call aqo_reset() at the start of each test.And a bit more:1. Avoid to show a number of records in AQO ML storage - it candepend on optimizer settings and quite unstable (in progress).2. Use aliases query in output to avoid unstability of naming of anonymouscolumns.
1 parent75d30b8 commited0b103

File tree

44 files changed

+386
-335
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

44 files changed

+386
-335
lines changed

‎Makefile‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,12 @@ TAP_TESTS = 1
1616
REGRESS = aqo_dummy_test
1717
REGRESS_OPTS = --schedule=$(srcdir)/regress_schedule
1818

19+
# Set default values of some gucs to be stable on custom settings during
20+
# a kind of installcheck
21+
PGOPTIONS = --aqo.force_collect_stat=off --max_parallel_maintenance_workers=1\
22+
--aqo.join_threshold=0 --max_parallel_workers_per_gather=1
23+
exportPGOPTIONS
24+
1925
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
2026
stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements
2127
PG_CPPFLAGS += -I$(libpq_srcdir) -I$(fdw_srcdir) -I$(stat_srcdir)

‎aqo.conf‎

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

‎expected/aqo_controlled.out‎

Lines changed: 10 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,10 @@
1+
CREATE EXTENSION IF NOT EXISTS aqo;
2+
SELECT true AS success FROM aqo_reset();
3+
success
4+
---------
5+
t
6+
(1 row)
7+
18
CREATE TABLE aqo_test0(a int, b int, c int, d int);
29
WITH RECURSIVE t(a, b, c, d)
310
AS (
@@ -25,8 +32,6 @@ AS (
2532
) INSERT INTO aqo_test2 (SELECT * FROM t);
2633
CREATE INDEX aqo_test2_idx_a ON aqo_test2 (a);
2734
ANALYZE aqo_test2;
28-
CREATE EXTENSION aqo;
29-
SET aqo.join_threshold = 0;
3035
SET aqo.mode = 'controlled';
3136
EXPLAIN (COSTS FALSE)
3237
SELECT * FROM aqo_test0
@@ -199,11 +204,12 @@ WHERE t1.a = t2.b AND t2.a = t3.b;
199204

200205
SELECT count(*) FROM
201206
(SELECT queryid AS id FROM aqo_queries) AS q1,
202-
LATERAL aqo_queries_update(q1.id, NULL, NULL, true, NULL)
207+
LATERAL aqo_queries_update(q1.id, NULL, NULL, true, NULL) AS ret
208+
WHERE NOT ret
203209
; -- set use = true
204210
count
205211
-------
206-
12
212+
1
207213
(1 row)
208214

209215
EXPLAIN (COSTS FALSE)
@@ -311,11 +317,4 @@ DROP INDEX aqo_test1_idx_a;
311317
DROP TABLE aqo_test1;
312318
DROP INDEX aqo_test2_idx_a;
313319
DROP TABLE aqo_test2;
314-
-- XXX: extension dropping doesn't clear file storage. Do it manually.
315-
SELECT 1 FROM aqo_reset();
316-
?column?
317-
----------
318-
1
319-
(1 row)
320-
321320
DROP EXTENSION aqo;

‎expected/aqo_disabled.out‎

Lines changed: 13 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,12 @@
1+
-- Create the extension. Drop all lumps which could survive from
2+
-- previous pass (repeated installcheck as an example).
3+
CREATE EXTENSION IF NOT EXISTS aqo;
4+
SELECT true AS success FROM aqo_reset();
5+
success
6+
---------
7+
t
8+
(1 row)
9+
110
CREATE TABLE aqo_test0(a int, b int, c int, d int);
211
WITH RECURSIVE t(a, b, c, d)
312
AS (
@@ -16,8 +25,6 @@ AS (
1625
) INSERT INTO aqo_test1 (SELECT * FROM t);
1726
CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
1827
ANALYZE aqo_test1;
19-
CREATE EXTENSION aqo;
20-
SET aqo.join_threshold = 0;
2128
SET aqo.mode = 'controlled';
2229
CREATE TABLE tmp1 AS SELECT * FROM aqo_test0
2330
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
@@ -151,11 +158,12 @@ SELECT count(*) FROM aqo_queries WHERE queryid <> fs; -- Should be zero
151158
SET aqo.mode = 'controlled';
152159
SELECT count(*) FROM
153160
(SELECT queryid AS id FROM aqo_queries) AS q1,
154-
LATERAL aqo_queries_update(q1.id, NULL, true, true, false)
161+
LATERAL aqo_queries_update(q1.id, NULL, true, true, false) AS ret
162+
WHERE NOT ret
155163
; -- Enable all disabled query classes
156164
count
157165
-------
158-
5
166+
1
159167
(1 row)
160168

161169
EXPLAIN SELECT * FROM aqo_test0
@@ -223,15 +231,8 @@ SELECT count(*) FROM aqo_queries WHERE queryid <> fs; -- Should be zero
223231
0
224232
(1 row)
225233

226-
-- XXX: extension dropping doesn't clear file storage. Do it manually.
227-
SELECT 1 FROM aqo_reset();
228-
?column?
229-
----------
230-
1
231-
(1 row)
232-
233-
DROP EXTENSION aqo;
234234
DROP INDEX aqo_test0_idx_a;
235235
DROP TABLE aqo_test0;
236236
DROP INDEX aqo_test1_idx_a;
237237
DROP TABLE aqo_test1;
238+
DROP EXTENSION aqo;

‎expected/aqo_fdw.out‎

Lines changed: 21 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -3,12 +3,17 @@
33
-- JOIN push-down (check push of baserestrictinfo and joininfo)
44
-- Aggregate push-down
55
-- Push-down of groupings with HAVING clause.
6-
CREATE EXTENSION aqo;
7-
CREATE EXTENSION postgres_fdw;
6+
CREATE EXTENSION IF NOT EXISTS aqo;
7+
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
8+
SELECT true AS success FROM aqo_reset();
9+
success
10+
---------
11+
t
12+
(1 row)
13+
814
SET aqo.mode = 'learn';
915
SET aqo.show_details = 'true'; -- show AQO info for each node and entire query.
1016
SET aqo.show_hash = 'false'; -- a hash value is system-depended. Ignore it.
11-
SET aqo.join_threshold = 0;
1217
DO $d$
1318
BEGIN
1419
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
@@ -100,15 +105,23 @@ SELECT str FROM expln('
100105
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
101106
SELECT * FROM frgn AS a, frgn AS b WHERE a.x=b.x;
102107
') AS str WHERE str NOT LIKE '%Sort Method%';
103-
str
104-
-------------------------------------------
105-
Foreign Scan (actual rows=1 loops=1)
108+
str
109+
------------------------------------------------------------
110+
Merge Join (actual rows=1 loops=1)
106111
AQO not used
107-
Relations: (frgn a) INNER JOIN (frgn b)
112+
Merge Cond: (a.x = b.x)
113+
-> Sort (actual rows=1 loops=1)
114+
Sort Key: a.x
115+
-> Foreign Scan on frgn a (actual rows=1 loops=1)
116+
AQO not used
117+
-> Sort (actual rows=1 loops=1)
118+
Sort Key: b.x
119+
-> Foreign Scan on frgn b (actual rows=1 loops=1)
120+
AQO not used
108121
Using aqo: true
109122
AQO mode: LEARN
110123
JOINS: 0
111-
(6 rows)
124+
(14 rows)
112125

113126
-- Should learn on postgres_fdw nodes
114127
SELECT str FROM expln('

‎expected/aqo_forced.out‎

Lines changed: 8 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,11 @@
1+
-- Preliminaries
2+
CREATE EXTENSION IF NOT EXISTS aqo;
3+
SELECT true AS success FROM aqo_reset();
4+
success
5+
---------
6+
t
7+
(1 row)
8+
19
CREATE TABLE aqo_test0(a int, b int, c int, d int);
210
WITH RECURSIVE t(a, b, c, d)
311
AS (
@@ -16,8 +24,6 @@ AS (
1624
) INSERT INTO aqo_test1 (SELECT * FROM t);
1725
CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
1826
ANALYZE aqo_test1;
19-
CREATE EXTENSION aqo;
20-
SET aqo.join_threshold = 0;
2127
SET aqo.mode = 'controlled';
2228
EXPLAIN (COSTS FALSE)
2329
SELECT * FROM aqo_test0
@@ -82,11 +88,4 @@ DROP INDEX aqo_test0_idx_a;
8288
DROP TABLE aqo_test0;
8389
DROP INDEX aqo_test1_idx_a;
8490
DROP TABLE aqo_test1;
85-
-- XXX: extension dropping doesn't clear file storage. Do it manually.
86-
SELECT 1 FROM aqo_reset();
87-
?column?
88-
----------
89-
1
90-
(1 row)
91-
9291
DROP EXTENSION aqo;

‎expected/aqo_intelligent.out‎

Lines changed: 7 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,10 @@
1+
CREATE EXTENSION IF NOT EXISTS aqo;
2+
SELECT true AS success FROM aqo_reset();
3+
success
4+
---------
5+
t
6+
(1 row)
7+
18
CREATE TABLE aqo_test0(a int, b int, c int, d int);
29
WITH RECURSIVE t(a, b, c, d)
310
AS (
@@ -16,8 +23,6 @@ AS (
1623
) INSERT INTO aqo_test1 (SELECT * FROM t);
1724
CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
1825
ANALYZE aqo_test1;
19-
CREATE EXTENSION aqo;
20-
SET aqo.join_threshold = 0;
2126
SET aqo.mode = 'intelligent';
2227
EXPLAIN SELECT * FROM aqo_test0
2328
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
@@ -519,11 +524,4 @@ DROP INDEX aqo_test0_idx_a;
519524
DROP TABLE aqo_test0;
520525
DROP INDEX aqo_test1_idx_a;
521526
DROP TABLE aqo_test1;
522-
-- XXX: extension dropping doesn't clear file storage. Do it manually.
523-
SELECT 1 FROM aqo_reset();
524-
?column?
525-
----------
526-
1
527-
(1 row)
528-
529527
DROP EXTENSION aqo;

‎expected/aqo_learn.out‎

Lines changed: 12 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,10 @@
1+
CREATE EXTENSION IF NOT EXISTS aqo;
2+
SELECT true AS success FROM aqo_reset();
3+
success
4+
---------
5+
t
6+
(1 row)
7+
18
-- The function just copied from stats_ext.sql
29
create function check_estimated_rows(text) returns table (estimated int, actual int)
310
language plpgsql as
@@ -36,8 +43,6 @@ AS (
3643
) INSERT INTO aqo_test1 (SELECT * FROM t);
3744
CREATE INDEX aqo_test1_idx_a ON aqo_test1 (a);
3845
ANALYZE aqo_test1;
39-
CREATE EXTENSION aqo;
40-
SET aqo.join_threshold = 0;
4146
SET aqo.mode = 'intelligent';
4247
EXPLAIN SELECT * FROM aqo_test0
4348
WHERE a < 3 AND b < 3 AND c < 3 AND d < 3;
@@ -236,10 +241,10 @@ SELECT count(*) FROM tmp1;
236241
(1 row)
237242

238243
-- Remove data on some unneeded instances of tmp1 table.
239-
SELECT* FROM aqo_cleanup();
240-
nfs | nfss
241-
-----+------
242-
9 | 18
244+
SELECTtrue AS success FROM aqo_cleanup();
245+
success
246+
---------
247+
t
243248
(1 row)
244249

245250
-- Result of the query below should be empty
@@ -563,7 +568,7 @@ SELECT * FROM check_estimated_rows(
563568
'SELECT * FROM aqo_test1 AS t1, aqo_test1 AS t2 WHERE t1.a = t2.b');
564569
estimated | actual
565570
-----------+--------
566-
19 | 19
571+
20 | 19
567572
(1 row)
568573

569574
SELECT count(*) FROM
@@ -716,11 +721,4 @@ DROP INDEX aqo_test0_idx_a;
716721
DROP TABLE aqo_test0;
717722
DROP INDEX aqo_test1_idx_a;
718723
DROP TABLE aqo_test1;
719-
-- XXX: extension dropping doesn't clear file storage. Do it manually.
720-
SELECT 1 FROM aqo_reset();
721-
?column?
722-
----------
723-
1
724-
(1 row)
725-
726724
DROP EXTENSION aqo;

‎expected/clean_aqo_data.out‎

Lines changed: 20 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,10 @@
1-
CREATE EXTENSION aqo;
2-
SET aqo.join_threshold = 0;
1+
CREATE EXTENSION IF NOT EXISTS aqo;
2+
SELECT true AS success FROM aqo_reset();
3+
success
4+
---------
5+
t
6+
(1 row)
7+
38
SET aqo.mode = 'learn';
49
DROP TABLE IF EXISTS a;
510
NOTICE: table "a" does not exist, skipping
@@ -11,9 +16,9 @@ SELECT * FROM a;
1116
(0 rows)
1217

1318
SELECT 'a'::regclass::oid AS a_oid \gset
14-
SELECT true FROM aqo_cleanup();
15-
?column?
16-
----------
19+
SELECT trueAS successFROM aqo_cleanup();
20+
success
21+
---------
1722
t
1823
(1 row)
1924

@@ -54,9 +59,9 @@ SELECT count(*) FROM aqo_query_stat WHERE
5459
(1 row)
5560

5661
DROP TABLE a;
57-
SELECT true FROM aqo_cleanup();
58-
?column?
59-
----------
62+
SELECT trueAS successFROM aqo_cleanup();
63+
success
64+
---------
6065
t
6166
(1 row)
6267

@@ -119,7 +124,7 @@ SELECT 'b'::regclass::oid AS b_oid \gset
119124
SELECT count(*) FROM aqo_data WHERE :a_oid=ANY(oids);
120125
count
121126
-------
122-
2
127+
3
123128
(1 row)
124129

125130
SELECT count(*) FROM aqo_queries WHERE
@@ -175,9 +180,9 @@ SELECT count(*) FROM aqo_query_stat WHERE
175180
(1 row)
176181

177182
DROP TABLE a;
178-
SELECT true FROM aqo_cleanup();
179-
?column?
180-
----------
183+
SELECT trueAS successFROM aqo_cleanup();
184+
success
185+
---------
181186
t
182187
(1 row)
183188

@@ -253,9 +258,9 @@ SELECT count(*) FROM aqo_query_stat WHERE
253258
(1 row)
254259

255260
DROP TABLE b;
256-
SELECT true FROM aqo_cleanup();
257-
?column?
258-
----------
261+
SELECT trueAS successFROM aqo_cleanup();
262+
success
263+
---------
259264
t
260265
(1 row)
261266

0 commit comments

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