10000 Fix some problems found during underwent of the AQO by Join-Order-Ben… · postgrespro/aqo@1c3c088 · GitHub
[go: up one dir, main page]
Skip to content

Commit 1c3c088

Browse files
a.pervushinadanolivo
a.pervushina
authored and
danolivo
committed
Fix some problems found during underwent of the AQO by Join-Order-Benchmark:
1. Minor code improvements 2. Introduce the show_cardinality_errors(bool) routine that can show cardinality errors detected by the AQO that made during last execution under or without AQO control. 3. Ignore queries that don't touch any database relations.
1 parent 9c1bede commit 1c3c088

File tree

8 files changed

+176
-103
lines changed

8 files changed

+176
-103
lines changed

‎aqo--1.3--1.4.sql

Lines changed: 48 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -5,31 +5,60 @@
55

66
ALTER TABLE public.aqo_data ADD COLUMN reliability double precision [];
77

8+
DROP FUNCTION public.top_error_queries(int);
9+
10+
--
11+
-- Get cardinality error of queries the last time they were executed.
12+
-- IN:
13+
-- controlled - show queries executed under a control of AQO (true);
14+
-- executed without an AQO control, but AQO has a stat on the query (false).
815
--
9-
-- Get IDs of queries having the largest cardinality error when last executed.
16+
-- OUT:
1017
-- num - sequental number. Smaller number corresponds to higher error.
11-
-- qhash - ID of a query.
12-
-- error - AQO error calculated over plan nodes of the query.
18+
-- id - ID of a query.
19+
-- fshash - feature space. Usually equal to zero or ID.
20+
-- error - AQO error that calculated on plan nodes of the query.
21+
-- nexecs - number of executions of queries associated with this ID.
1322
--
14-
CREATE OR REPLACE FUNCTION public.show_cardinality_errors()
15-
RETURNS TABLE(num bigint, id bigint, error float)
23+
CREATE OR REPLACE FUNCTION public.show_cardinality_errors(controlled boolean)
24+
RETURNS TABLE(num bigint, id bigint, fshash bigint, error float, nexecs bigint)
1625
AS $$
1726
BEGIN
27+
IF (controlled) THEN
28+
RETURN QUERY
29+
SELECT
30+
row_number() OVER (ORDER BY (cerror, query_id, fs_hash) DESC) AS nn,
31+
query_id, fs_hash, cerror, execs
32+
FROM (
33+
SELECT
34+
aq.query_hash AS query_id,
35+
aq.fspace_hash AS fs_hash,
36+
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo, 1)] AS cerror,
37+
executions_with_aqo AS execs
38+
FROM public.aqo_queries aq JOIN public.aqo_query_stat aqs
39+
ON aq.query_hash = aqs.query_hash
40+
WHERE TRUE = ANY (SELECT unnest(cardinality_error_with_aqo) IS NOT NULL)
41+
) AS q1
42+
ORDER BY nn ASC;
43+
ELSE
1844
RETURN QUERY
19-
SELECT
20-
row_number() OVER (ORDER BY (cerror, qhash) DESC) AS nn,
21-
qhash, cerror
22-
FROM (
23-
SELECT
24-
aq.query_hash AS qhash,
25-
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo, 1)] AS cerror
26-
FROM public.aqo_queries aq JOIN public.aqo_query_stat aqs
27-
ON aq.query_hash = aqs.query_hash
28-
WHERE TRUE = ANY (SELECT unnest(cardinality_error_with_aqo) IS NOT NULL)
29-
) AS q1
30-
ORDER BY nn ASC;
45+
SELECT
46+
row_number() OVER (ORDER BY (cerror, query_id, fs_hash) DESC) AS nn,
47+
query_id, fs_hash, cerror, execs
48+
FROM (
49+
SELECT
50+
aq.query_hash AS query_id,
51+
aq.fspace_hash AS fs_hash,
52+
array_avg(cardinality_error_without_aqo) AS cerror,
53+
executions_without_aqo AS execs
54+
FROM public.aqo_queries aq JOIN public.aqo_query_stat aqs
55+
ON aq.query_hash = aqs.query_hash
56+
WHERE TRUE = ANY (SELECT unnest(cardinality_error_without_aqo) IS NOT NULL)
57+
) AS q1
58+
ORDER BY (nn) ASC;
59+
END IF;
3160
END;
3261
$$ LANGUAGE plpgsql;
3362

34-
COMMENT ON FUNCTION public.show_cardinality_errors() IS
35-
'Get cardinality error of last query execution. Return queries having the largest error.';
63+
COMMENT ON FUNCTION public.show_cardinality_errors(boolean) IS
64+
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';

‎expected/gucs.out

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,9 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
3434

3535
-- Check existence of the interface functions.
3636
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
37-
obj_description
38-
-----------------------------------------------------------------------------------------
39-
Get cardinality error of last query execution. Return queries having the largest error.
37+
obj_description
38+
---------------------------------------------------------------------------------------------------------------
39+
Get cardinality error of queries the last time they were executed. Order queries according to an error value.
4040
(1 row)
4141

4242
DROP EXTENSION aqo;

‎expected/top_queries.out

Lines changed: 37 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -6,23 +6,31 @@ CREATE EXTENSION aqo;
66
SET aqo.mode = 'disabled';
77
SET aqo.force_collect_stat = 'on';
88
--
9-
-- num of generate_series(1,1000000) query should be the first
9+
-- Dummy test. CREATE TABLE shouldn't find in the ML storage. But a simple
10+
-- select must be in. Also here we test on gathering a stat on temp and plain
11+
-- relations.
1012
--
11-
SELECT count(*) FROM generate_series(1,1000000);
12-
count
13-
---------
14-
1000000
13+
CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);
14+
CREATE TABLE ttp AS SELECT count(*) AS cnt FROM generate_series(1,10);
15+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0;
16+
cnt
17+
-----
18+
0
19+
(1 row)
20+
21+
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0;
22+
cnt
23+
-----
24+
0
1525
(1 row)
1626

17-
SELECT num FROM top_time_queries(10) AS tt WHERE
18-
tt.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE
19-
aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts
20-
WHERE query_text = 'SELECT count(*) FROM generate_series(1,1000000);'));
21-
NOTICE: Top 10 execution time queries
27+
SELECT num FROM top_time_queries(3);
28+
NOTICE: Top 3 execution time queries
2229
num
2330
-----
2431
1
25-
(1 row)
32+
2
33+
(2 rows)
2634

2735
--
2836
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
@@ -43,13 +51,23 @@ SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y
4351
31
4452
(1 row)
4553

46-
SELECT num FROM top_error_queries(10) AS te WHERE
47-
te.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE
48-
aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts
49-
WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'));
50-
NOTICE: Top 10 cardinality error queries
51-
num
52-
-----
53-
1
54+
SELECT num, to_char(error, '9.99EEEE') FROM show_cardinality_errors(false) AS te
55+
WHERE te.fshash = (
56+
SELECT fspace_hash FROM aqo_queries
57+
WHERE aqo_queries.query_hash = (
58+
SELECT aqo_query_texts.query_hash FROM aqo_query_texts
59+
WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'
60+
)
61+
);
62+
num | to_char
63+
-----+-----------
64+
1 | 1.94e+00
65+
(1 row)
66+
67+
-- Should return zero
68+
SELECT count(*) FROM show_cardinality_errors(true);
69+
count
70+
-------
71+
0
5472
(1 row)
5573

‎expected/unsupported.out

Lines changed: 44 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -557,52 +557,59 @@ EXPLAIN (COSTS OFF)
557557
-- XXX: Do we stuck into an unstable behavior of an error value?
558558
-- Live with this variant of the test for some time.
559559
SELECT
560-
num,
561-
to_char(error, '9.99EEEE')::text AS error
562-
FROM public.show_cardinality_errors()
563-
WHERE error > 0.;
564-
num | error
565-
-----+-----------
566-
1 | 9.69e+02
567-
2 | 1.15e+02
568-
3 | 3.00e+01
569-
4 | 3.00e+01
570-
5 | 3.00e+01
571-
6 | 1.33e+00
572-
(6 rows)
560+
num, to_char(error, '9.99EEEE')::text AS error, query_text
561+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
562+
WHERE aqt.query_hash = cef.id
563+
ORDER BY (error, md5(query_text)) DESC;
564+
num | error | query_text
565+
-----+-----------+------------------------------------------------------------------------------------------------
566+
1 | 1.15e+02 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
567+
3 | 3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
568+
4 | 3.00e+01 | SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
569+
2 | 3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,x*y)) AS q1;
570+
5 | 1.33e+00 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
571+
11 | 0.00e+00 | SELECT * FROM +
572+
| | (SELECT * FROM t WHERE x < 0) AS t0 +
573+
| | JOIN +
574+
| | (SELECT * FROM t WHERE x > 20) AS t1 +
575+
| | USING(x);
576+
10 | 0.00e+00 | SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
577+
12 | 0.00e+00 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
578+
| | SELECT count(*) FROM t WHERE +
579+
| | x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21) OR +
580+
| | x IN (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21);
581+
8 | 0.00e+00 | SELECT count(*) FROM ( +
582+
| | SELECT count(*) AS x FROM ( +
583+
| | SELECT count(*) FROM t1 GROUP BY (x,y) +
584+
| | ) AS q1 +
585+
| | ) AS q2 +
586+
| | WHERE q2.x > 1;
587+
9 | 0.00e+00 | SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
588+
6 | 0.00e+00 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
589+
| | SELECT count(*) FROM t WHERE (SELECT avg(x) FROM t t0 WHERE t0.x = t.x) = +
590+
| | (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
591+
7 | 0.00e+00 | SELECT count(*) FROM +
592+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +
593+
| | JOIN +
594+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2 +
595+
| | ON q1.x = q2.x+1;
596+
(12 rows)
573597

574598
DROP TABLE t,t1 CASCADE;
575599
SELECT public.clean_aqo_data();
576600
NOTICE: Cleaning aqo_data records
577-
clean_aqo_data
601+
clean_aqo_data
578602
----------------
579-
603+
580604
(1 row)
581605

582-
-- TODO: figure out with remaining queries in the ML storage.
606+
-- Look for any remaining queries in the ML storage.
583607
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
584-
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
608+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
585609
WHERE aqt.query_hash = cef.id
586610
ORDER BY (error, md5(query_text)) DESC;
587-
num | error | query_text
588-
-----+-----------+-------------------------------------------------------------------------------------------
589-
1 | 9.69e+02 | SELECT str FROM expln(' +
590-
| | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
591-
| | SELECT count(*) FROM +
592-
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +
593-
| | JOIN +
594-
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2+
595-
| | ON q1.x = q2.x+1; +
596-
| | ') AS str WHERE str NOT LIKE '%Memory Usage%';
597-
2 | 3.27e+02 | SELECT +
598-
| | num, +
599-
| | to_char(error, '9.99EEEE')::text AS error +
600-
| | FROM public.show_cardinality_errors() +
601-
| | WHERE error > 0.;
602-
5 | 0.00e+00 | CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
603-
4 | 0.00e+00 | SELECT public.clean_aqo_data();
604-
3 | 0.00e+00 | CREATE TABLE t1 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y +
605-
| | FROM generate_series(1,1000) AS gs;
606-
(5 rows)
611+
num | error | query_text
612+
-----+-------+------------
613+
(0 rows)
607614

608615
DROP EXTENSION aqo;

‎preprocessing.c

Lines changed: 12 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -407,12 +407,19 @@ disable_aqo_for_query(void)
407407

408408
/*
409409
* Examine a fully-parsed query, and return TRUE iff any relation underlying
410-
* the query is a system relation.
410+
* the query is a system relation or no one relation touched by the query.
411411
*/
412412
static bool
413413
isQueryUsingSystemRelation(Query *query)
414414
{
415-
return isQueryUsingSystemRelation_walker((Node *) query, NULL);
415+
bool trivQuery = true;
416+
bool result;
417+
418+
result = isQueryUsingSystemRelation_walker((Node *) query, &trivQuery);
419+
420+
if (result || trivQuery)
421+
return true;
422+
return false;
416423
}
417424

418425

@@ -451,10 +458,13 @@ isQueryUsingSystemRelation_walker(Node *node, void *context)
451458
Relation rel = table_open(rte->relid, AccessShareLock);
452459
bool is_catalog = IsCatalogRelation(rel);
453460
bool is_aqo_rel = IsAQORelation(rel);
461+
bool *trivQuery = (bool *) context;
454462

455463
table_close(rel, AccessShareLock);
456464
if (is_catalog || is_aqo_rel)
457465
return true;
466+
467+
*trivQuery = false;
458468
}
459469
else if (rte->rtekind == RTE_FUNCTION)
460470
{

‎sql/top_queries.sql

Lines changed: 19 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,13 +8,15 @@ SET aqo.mode = 'disabled';
88
SET aqo.force_collect_stat = 'on';
99

1010
--
11-
-- num of generate_series(1,1000000) query should be the first
11+
-- Dummy test. CREATE TABLE shouldn't find in the ML storage. But a simple
12+
-- select must be in. Also here we test on gathering a stat on temp and plain
13+
-- relations.
1214
--
13-
SELECT count(*) FROM generate_series(1,1000000);
14-
SELECT num FROM top_time_queries(10) AS tt WHERE
15-
tt.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE
16-
aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts
17-
WHERE query_text = 'SELECT count(*) FROM generate_series(1,1000000);'));
15+
CREATE TEMP TABLE ttt AS SELECT count(*) AS cnt FROM generate_series(1,10);
16+
CREATE TABLE ttp AS SELECT count(*) AS cnt FROM generate_series(1,10);
17+
SELECT count(*) AS cnt FROM ttt WHERE cnt % 100 = 0;
18+
SELECT count(*) AS cnt FROM ttp WHERE cnt % 100 = 0;
19+
SELECT num FROM top_time_queries(3);
1820

1921
--
2022
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
@@ -26,7 +28,14 @@ CREATE TABLE t2 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y
2628
SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
2729
SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
2830

29-
SELECT num FROM top_error_queries(10) AS te WHERE
30-
te.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE
31-
aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts
32-
WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'));
31+
SELECT num, to_char(error, '9.99EEEE') FROM show_cardinality_errors(false) AS te
32+
WHERE te.fshash = (
< 10000 /code>
33+
SELECT fspace_hash FROM aqo_queries
34+
WHERE aqo_queries.query_hash = (
35+
SELECT aqo_query_texts.query_hash FROM aqo_query_texts
36+
WHERE query_text = 'SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'
37+
)
38+
);
39+
40+
-- Should return zero
41+
SELECT count(*) FROM show_cardinality_errors(true);

‎sql/unsupported.sql

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -175,18 +175,18 @@ EXPLAIN (COSTS OFF)
175175
-- XXX: Do we stuck into an unstable behavior of an error value?
176176
-- Live with this variant of the test for some time.
177177
SELECT
178-
num,
179-
to_char(error, '9.99EEEE')::text AS error
180-
FROM public.show_cardinality_errors()
181-
WHERE error > 0.;
178+
num, to_char(error, '9.99EEEE')::text AS error, query_text
179+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
180+
WHERE aqt.query_hash = cef.id
181+
ORDER BY (error, md5(query_text)) DESC;
182182

183183
DROP TABLE t,t1 CASCADE;
184184

185185
SELECT public.clean_aqo_data();
186186

187-
-- TODO: figure out with remaining queries in the ML storage.
187+
-- Look for any remaining queries in the ML storage.
188188
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
189-
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
189+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
190190
WHERE aqt.query_hash = cef.id
191191
ORDER BY (error, md5(query_text)) DESC;
192192

‎t/001_pgbench.pl

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -134,24 +134,24 @@
134134
'analytical queries in pgbench (disabled mode)');
135135

136136
$res = $node->safe_psql('postgres',
137-
"SELECT count(*) FROM top_error_queries(10) v
138-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
137+
"SELECT count(*) FROM show_cardinality_errors(false) v
138+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
139139
WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");
140140
is($res, 3);
141141
$res = $node->safe_psql('postgres',
142-
"SELECT * FROM top_error_queries(10) v
143-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
142+
"SELECT * FROM show_cardinality_errors(false) v
143+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
144144
WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");
145145
note("\n TopN: \n $res \n");
146146
$res = $node->safe_psql('postgres',
147-
"SELECT v.error, t.query_text FROM top_error_queries(10) v
148-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
147+
"SELECT v.error, t.query_text FROM show_cardinality_errors(false) v
148+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
149149
WHERE v.error > 0.");
150150
note("\n Queries: \n $res \n");
151151
$res = $node->safe_psql('postgres',
152152
"SELECT count(*) FROM top_time_queries(10) v
153153
WHERE v.execution_time > 0.");
154-
is($res, 5);
154+
is($res, 3);
155155

156156
# ##############################################################################
157157
#

0 commit comments

Comments
 (0)
0