8000 [PGPRO-6755] Refactor machine dependent tests · postgrespro/aqo@0cfcab8 · GitHub
[go: up one dir, main page]
Skip to content

Commit 0cfcab8

Browse files
a.pervushinaAlena Rybakina
a.pervushina
authored and
Alena Rybakina
committed
[PGPRO-6755] Refactor machine dependent tests
Tags: aqo
1 parent 8c6c1ca commit 0cfcab8

File tree

4 files changed

+56
-16
lines changed

4 files changed

+56
-16
lines changed

‎expected/forced_stat_collection.out

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -38,14 +38,21 @@ SELECT * FROM aqo_data;
3838
----+-----+-----------+----------+---------+-------------+------
3939
(0 rows)
4040

41-
SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex
41+
CREATE OR REPLACE FUNCTION round_array (double precision[])
42+
RETURNS double precision[]
43+
LANGUAGE SQL
44+
AS $$
45+
SELECT array_agg(round(elem::numeric, 3))
46+
FROM unnest($1) as arr(elem);
47+
$$;
48+
SELECT learn_aqo,use_aqo,auto_tuning,round_array(cardinality_error_without_aqo) ce,executions_without_aqo nex
4249
FROM aqo_queries AS aq JOIN aqo_query_stat AS aqs
4350
ON aq.queryid = aqs.queryid
4451
ORDER BY (cardinality_error_without_aqo);
45-
learn_aqo | use_aqo | auto_tuning | ce | nex
46-
-----------+---------+-------------+----------------------+-----
47-
f | f | f | {0.8637762840285226} | 1
48-
f | f | f | {2.9634630129852053} | 1
52+
learn_aqo | use_aqo | auto_tuning | ce | nex
53+
-----------+---------+-------------+---------+-----
54+
f | f | f | {0.864} | 1
55+
f | f | f | {2.963} | 1
4956
(2 rows)
5057

5158
SELECT query_text FROM aqo_query_texts ORDER BY (md5(query_text));

‎expected/unsupported.out

Lines changed: 20 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -467,6 +467,17 @@ SELECT * FROM
467467
-- any prediction on number of fetched tuples.
468468
-- So, if selectivity was wrong we could make bad choice of Scan operation.
469469
-- For example, we could choose suboptimal index.
470+
--
471+
-- Returns string-by-string explain of a query. Made for removing some strings
472+
-- from the explain output.
473+
--
474+
CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$
475+
BEGIN
476+
RETURN QUERY
477+
EXECUTE format('EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF) %s', query_string);
478+
RETURN;
479+
END;
480+
$$ LANGUAGE PLPGSQL;
470481
-- Turn off statistics gathering for simple demonstration of filtering problem.
471482
ALTER TABLE t SET (autovacuum_enabled = 'false');
472483
CREATE INDEX ind1 ON t(x);
@@ -502,21 +513,22 @@ SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
502513
50
503514
(1 row)
504515

505-
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
506-
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
507-
QUERY PLAN
516+
SELECT str AS result
517+
FROM expln('SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1') AS str
518+
WHERE str NOT LIKE '%Heap Blocks%';
519+
result
508520
-----------------------------------------------------------------
509521
Aggregate (actual rows=1 loops=1)
510522
AQO not used
511-
-> Bitmap Heap Scan on t (actual rows=50 loops=1)
523+
Output: count(*)
524+
-> Bitmap Heap Scan on public.t (actual rows=50 loops=1)
512525
AQO: rows=50, error=0%
513-
Recheck Cond: (mod(x, 3) = 1)
514-
Filter: (x < 3)
526+
Recheck Cond: (mod(t.x, 3) = 1)
527+
Filter: (t.x < 3)
515528
Rows Removed by Filter: 300
516-
Heap Blocks: exact=5
517529
-> Bitmap Index Scan on ind2 (actual rows=350 loops=1)
518530
AQO not used
519-
Index Cond: (mod(x, 3) = 1)
531+
Index Cond: (mod(t.x, 3) = 1)
520532
Using aqo: true
521533
AQO mode: LEARN
522534
JOINS: 0

‎sql/forced_stat_collection.sql

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,15 @@ SELECT count(*) FROM person WHERE age<18;
3030
SELECT count(*) FROM person WHERE age<18 AND passport IS NOT NULL;
3131
SELECT * FROM aqo_data;
3232

33-
SELECT learn_aqo,use_aqo,auto_tuning,cardinality_error_without_aqo ce,executions_without_aqo nex
33+
CREATE OR REPLACE FUNCTION round_array (double precision[])
34+
RETURNS double precision[]
35+
LANGUAGE SQL
36+
AS $$
37+
SELECT array_agg(round(elem::numeric, 3))
38+
FROM unnest($1) as arr(elem);
39+
$$;
40+
41+
SELECT learn_aqo,use_aqo,auto_tuning,round_array(cardinality_error_without_aqo) ce,executions_without_aqo nex
3442
FROM aqo_queries AS aq JOIN aqo_query_stat AS aqs
3543
ON aq.queryid = aqs.queryid
3644
ORDER BY (cardinality_error_without_aqo);

‎sql/unsupported.sql

Lines changed: 15 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -135,6 +135,18 @@ SELECT * FROM
135135
-- So, if selectivity was wrong we could make bad choice of Scan operation.
136136
-- For example, we could choose suboptimal index.
137137

138+
--
139+
-- Returns string-by-string explain of a query. Made for removing some strings
140+
-- from the explain output.
141+
--
142+
CREATE OR REPLACE FUNCTION expln(query_string text) RETURNS SETOF text AS $$
143+
BEGIN
144+
RETURN QUERY
145+
EXECUTE format('EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF) %s', query_string);
146+
RETURN;
147+
END;
148+
$$ LANGUAGE PLPGSQL;
149+
138150
-- Turn off statistics gathering for simple demonstration of filtering problem.
139151
ALTER TABLE t SET (autovacuum_enabled = 'false');
140152
CREATE INDEX ind1 ON t(x);
@@ -147,8 +159,9 @@ EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
147159
-- Here we filter more tuples than with the ind1 index.
148160
CREATE INDEX ind2 ON t(mod(x,3));
149161
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
150-
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
151-
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
162+
SELECT str AS result
163+
FROM expln('SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1') AS str
164+
WHERE str NOT LIKE '%Heap Blocks%';
152165

153166
-- Best choice is ...
154167
ANALYZE t;

0 commit comments

Comments
 (0)
0