8000 [PGPRO-6755] Refactor machine dependent tests · postgrespro/aqo@0cfcab8 · GitHub
[go: up one dir, main page]
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
Appearance settings

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