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

Commitde286fe

Browse files
committed
Fix some problems found during underwent of the AQO by Join-Order-Benchmark:
1. Minor code improvements2. Introduce the show_cardinality_errors(bool) routine that can showcardinality errors detected by the AQO that made during last executionunder or without AQO control.3. Ignore queries that don't touch any database relations.
1 parentafd6e98 commitde286fe

File tree

8 files changed

+174
-101
lines changed

8 files changed

+174
-101
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
ALTERTABLEpublic.aqo_data ADD COLUMN reliabilitydouble precision [];
77

8+
DROPFUNCTIONpublic.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 REPLACEFUNCTIONpublic.show_cardinality_errors()
15-
RETURNS TABLE(numbigint, idbigint, error float)
23+
CREATE OR REPLACEFUNCTIONpublic.show_cardinality_errors(controlledboolean)
24+
RETURNS TABLE(numbigint, idbigint,fshashbigint,error float, nexecsbigint)
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_hashAS query_id,
35+
aq.fspace_hashAS fs_hash,
36+
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror,
37+
executions_with_aqoAS execs
38+
FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs
39+
ONaq.query_hash=aqs.query_hash
40+
WHERE TRUE= ANY (SELECT unnest(cardinality_error_with_aqo)IS NOT NULL)
41+
)AS q1
42+
ORDER BY nnASC;
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_hashAS qhash,
25-
cardinality_error_with_aqo[array_length(cardinality_error_with_aqo,1)]AS cerror
26-
FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs
27-
ONaq.query_hash=aqs.query_hash
28-
WHERE TRUE= ANY (SELECT unnest(cardinality_error_with_aqo)IS NOT NULL)
29-
)AS q1
30-
ORDER BY nnASC;
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_hashAS query_id,
51+
aq.fspace_hashAS fs_hash,
52+
array_avg(cardinality_error_without_aqo)AS cerror,
53+
executions_without_aqoAS execs
54+
FROMpublic.aqo_queries aqJOINpublic.aqo_query_stat aqs
55+
ONaq.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 lastquery execution. Return querieshaving the largest error.';
63+
COMMENT ON FUNCTION public.show_cardinality_errors(boolean) IS
64+
'Get cardinality error ofqueries thelasttime they were executed. Order queriesaccording to an error value.';

‎expected/gucs.out‎

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

3131
-- Check existence of the interface functions.
3232
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
33-
obj_description
34-
-----------------------------------------------------------------------------------------
35-
Get cardinality error of lastquery execution. Return querieshaving the largest error.
33+
obj_description
34+
---------------------------------------------------------------------------------------------------------------
35+
Get cardinality error ofqueries thelasttime they were executed. Order queriesaccording to an error value.
3636
(1 row)
3737

3838
DROP EXTENSION aqo;

‎expected/top_queries.out‎

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

13-
SELECT num FROM top_time_queries(10) AS tt WHERE
14-
tt.fspace_hash = (SELECT fspace_hash FROM aqo_queries WHERE
15-
aqo_queries.query_hash = (SELECT aqo_query_texts.query_hash FROM aqo_query_texts
16-
WHERE query_text = 'SELECT count(*) FROM generate_series(1,1000000);'));
17-
NOTICE: Top 10 execution time queries
23+
SELECT num FROM top_time_queries(3);
24+
NOTICE: Top 3 execution time queries
1825
num
1926
-----
2027
1
21-
(1 row)
28+
2
29+
(2 rows)
2230

2331
--
2432
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
@@ -39,13 +47,23 @@ SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y
3947
31
4048
(1 row)
4149

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

‎expected/unsupported.out‎

Lines changed: 42 additions & 35 deletions
Original file line numberDiff line numberDiff line change
@@ -541,19 +541,43 @@ EXPLAIN (COSTS OFF)
541541
-- XXX: Do we stuck into an unstable behavior of an error value?
542542
-- Live with this variant of the test for some time.
543543
SELECT
544-
num,
545-
to_char(error, '9.99EEEE')::text AS error
546-
FROM public.show_cardinality_errors()
547-
WHERE error > 0.;
548-
num | error
549-
-----+-----------
550-
1 | 9.69e+02
551-
2 | 1.15e+02
552-
3 | 3.00e+01
553-
4 | 3.00e+01
554-
5 | 3.00e+01
555-
6 | 1.33e+00
556-
(6 rows)
544+
num, to_char(error, '9.99EEEE')::text AS error, query_text
545+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
546+
WHERE aqt.query_hash = cef.id
547+
ORDER BY (error, md5(query_text)) DESC;
548+
num | error | query_text
549+
-----+-----------+------------------------------------------------------------------------------------------------
550+
1 | 1.15e+02 | SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
551+
3 | 3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,y)) AS q1;
552+
4 | 3.00e+01 | SELECT count(*) FROM (SELECT x, y FROM t1 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;
553+
2 | 3.00e+01 | SELECT count(*) FROM (SELECT count(*) FROM t1 GROUP BY (x,x*y)) AS q1;
554+
5 | 1.33e+00 | SELECT count(*) FROM (SELECT * FROM t GROUP BY (x) HAVING x > 3) AS q1;
555+
11 | 0.00e+00 | SELECT * FROM +
556+
| | (SELECT * FROM t WHERE x < 0) AS t0 +
557+
| | JOIN +
558+
| | (SELECT * FROM t WHERE x > 20) AS t1 +
559+
| | USING(x);
560+
10 | 0.00e+00 | SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
561+
12 | 0.00e+00 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
562+
| | SELECT count(*) FROM t WHERE +
563+
| | x = (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21) OR +
564+
| | x IN (SELECT avg(x) FROM t t0 WHERE t0.x = t.x + 21);
565+
8 | 0.00e+00 | SELECT count(*) FROM ( +
566+
| | SELECT count(*) AS x FROM ( +
567+
| | SELECT count(*) FROM t1 GROUP BY (x,y) +
568+
| | ) AS q1 +
569+
| | ) AS q2 +
570+
| | WHERE q2.x > 1;
571+
9 | 0.00e+00 | SELECT count(*) FROM t WHERE x = (SELECT avg(x) FROM t WHERE x = 1);
572+
6 | 0.00e+00 | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
573+
| | SELECT count(*) FROM t WHERE (SELECT avg(x) FROM t t0 WHERE t0.x = t.x) = +
574+
| | (SELECT avg(x) FROM t t0 WHERE t0.x = t.x);
575+
7 | 0.00e+00 | SELECT count(*) FROM +
576+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +
577+
| | JOIN +
578+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2 +
579+
| | ON q1.x = q2.x+1;
580+
(12 rows)
557581

558582
DROP TABLE t,t1 CASCADE;
559583
SELECT public.clean_aqo_data();
@@ -563,30 +587,13 @@ NOTICE: Cleaning aqo_data records
563587

564588
(1 row)
565589

566-
--TODO: figure out with remaining queries in the ML storage.
590+
--Look for any remaining queries in the ML storage.
567591
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
568-
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
592+
FROM public.show_cardinality_errors(true) cef, aqo_query_texts aqt
569593
WHERE aqt.query_hash = cef.id
570594
ORDER BY (error, md5(query_text)) DESC;
571-
num | error | query_text
572-
-----+-----------+-------------------------------------------------------------------------------------------
573-
1 | 9.69e+02 | SELECT str FROM expln(' +
574-
| | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
575-
| | SELECT count(*) FROM +
576-
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +
577-
| | JOIN +
578-
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2+
579-
| | ON q1.x = q2.x+1; +
580-
| | ') AS str WHERE str NOT LIKE '%Memory Usage%';
581-
2 | 3.27e+02 | SELECT +
582-
| | num, +
583-
| | to_char(error, '9.99EEEE')::text AS error +
584-
| | FROM public.show_cardinality_errors() +
585-
| | WHERE error > 0.;
586-
5 | 0.00e+00 | CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
587-
4 | 0.00e+00 | SELECT public.clean_aqo_data();
588-
3 | 0.00e+00 | CREATE TABLE t1 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y +
589-
| | FROM generate_series(1,1000) AS gs;
590-
(5 rows)
595+
num | error | query_text
596+
-----+-------+------------
597+
(0 rows)
591598

592599
DROP EXTENSION aqo;

‎preprocessing.c‎

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

369369
/*
370370
* Examine a fully-parsed query, and return TRUE iff any relation underlying
371-
* the query is a system relation.
371+
* the query is a system relation or no one relation touched by the query.
372372
*/
373373
staticbool
374374
isQueryUsingSystemRelation(Query*query)
375375
{
376-
returnisQueryUsingSystemRelation_walker((Node*)query,NULL);
376+
booltrivQuery= true;
377+
boolresult;
378+
379+
result=isQueryUsingSystemRelation_walker((Node*)query,&trivQuery);
380+
381+
if (result||trivQuery)
382+
return true;
383+
return false;
377384
}
378385

379386

@@ -412,10 +419,13 @@ isQueryUsingSystemRelation_walker(Node *node, void *context)
412419
Relationrel=table_open(rte->relid,AccessShareLock);
413420
boolis_catalog=IsCatalogRelation(rel);
414421
boolis_aqo_rel=IsAQORelation(rel);
422+
bool*trivQuery= (bool*)context;
415423

416424
table_close(rel,AccessShareLock);
417425
if (is_catalog||is_aqo_rel)
418426
return true;
427+
428+
*trivQuery= false;
419429
}
420430
elseif (rte->rtekind==RTE_FUNCTION)
421431
{

‎sql/top_queries.sql‎

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

55
--
6-
-- num of generate_series(1,1000000) query should be the first
6+
-- Dummy test. CREATE TABLE shouldn't find in the ML storage. But a simple
7+
-- select must be in. Also here we test on gathering a stat on temp and plain
8+
-- relations.
79
--
8-
SELECTcount(*)FROM generate_series(1,1000000);
9-
SELECT numFROM top_time_queries(10)ASttWHERE
10-
tt.fspace_hash= (SELECT fspace_hashFROM aqo_queriesWHERE
11-
aqo_queries.query_hash= (SELECTaqo_query_texts.query_hashFROM aqo_query_texts
12-
WHERE query_text='SELECTcount(*) FROMgenerate_series(1,1000000);'));
10+
CREATE TEMP TABLE tttASSELECTcount(*)AS cntFROM generate_series(1,10);
11+
CREATETABLEttpASSELECTcount(*)AScntFROM generate_series(1,10);
12+
SELECTcount(*)AS cntFROM tttWHERE cnt %100=0;
13+
SELECTcount(*)AS cntFROM ttpWHERE cnt %100=0;
14+
SELECTnumFROMtop_time_queries(3);
1315

1416
--
1517
-- num of query uses table t2 should be bigger than num of query uses table t1 and be the first
@@ -21,7 +23,14 @@ CREATE TABLE t2 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y
2123
SELECTcount(*)FROM (SELECT x, yFROM t1GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;
2224
SELECTcount(*)FROM (SELECT x, yFROM t2GROUP BY GROUPING SETS ((x,y), (x), (y), ()))AS q1;
2325

24-
SELECT numFROM top_error_queries(10)AS teWHERE
25-
te.fspace_hash= (SELECT fspace_hashFROM aqo_queriesWHERE
26-
aqo_queries.query_hash= (SELECTaqo_query_texts.query_hashFROM aqo_query_texts
27-
WHERE query_text='SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'));
26+
SELECT num, to_char(error,'9.99EEEE')FROM show_cardinality_errors(false)AS te
27+
WHEREte.fshash= (
28+
SELECT fspace_hashFROM aqo_queries
29+
WHEREaqo_queries.query_hash= (
30+
SELECTaqo_query_texts.query_hashFROM aqo_query_texts
31+
WHERE query_text='SELECT count(*) FROM (SELECT x, y FROM t2 GROUP BY GROUPING SETS ((x,y), (x), (y), ())) AS q1;'
32+
)
33+
);
34+
35+
-- Should return zero
36+
SELECTcount(*)FROM show_cardinality_errors(true);

‎sql/unsupported.sql‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -157,18 +157,18 @@ EXPLAIN (COSTS OFF)
157157
-- XXX: Do we stuck into an unstable behavior of an error value?
158158
-- Live with this variant of the test for some time.
159159
SELECT
160-
num,
161-
to_char(error,'9.99EEEE')::textAS error
162-
FROMpublic.show_cardinality_errors()
163-
WHEREerror>0.;
160+
num, to_char(error,'9.99EEEE')::textAS error, query_text
161+
FROMpublic.show_cardinality_errors(true) cef, aqo_query_texts aqt
162+
WHEREaqt.query_hash=cef.id
163+
ORDER BY (error, md5(query_text))DESC;
164164

165165
DROPTABLE t,t1 CASCADE;
166166

167167
SELECTpublic.clean_aqo_data();
168168

169-
--TODO: figure out with remaining queries in the ML storage.
169+
--Look for any remaining queries in the ML storage.
170170
SELECT num, to_char(error,'9.99EEEE')::textAS error, query_text
171-
FROMpublic.show_cardinality_errors() cef, aqo_query_texts aqt
171+
FROMpublic.show_cardinality_errors(true) cef, aqo_query_texts aqt
172172
WHEREaqt.query_hash=cef.id
173173
ORDER BY (error, md5(query_text))DESC;
174174

‎t/001_pgbench.pl‎

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

135135
$res =$node->safe_psql('postgres',
136-
"SELECT count(*) FROMtop_error_queries(10) v
137-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
136+
"SELECT count(*) FROMshow_cardinality_errors(false) v
137+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
138138
WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");
139139
is($res, 3);
140140
$res =$node->safe_psql('postgres',
141-
"SELECT * FROMtop_error_queries(10) v
142-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
141+
"SELECT * FROMshow_cardinality_errors(false) v
142+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
143143
WHERE v.error > 0. AND t.query_text LIKE '%pgbench_accounts%'");
144144
note("\n TopN:\n$res\n");
145145
$res =$node->safe_psql('postgres',
146-
"SELECT v.error, t.query_text FROMtop_error_queries(10) v
147-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
146+
"SELECT v.error, t.query_text FROMshow_cardinality_errors(false) v
147+
JOIN aqo_query_texts t ON (t.query_hash = v.id)
148148
WHERE v.error > 0.");
149149
note("\n Queries:\n$res\n");
150150
$res =$node->safe_psql('postgres',
151151
"SELECT count(*) FROM top_time_queries(10) v
152152
WHERE v.execution_time > 0.");
153-
is($res,5);
153+
is($res,3);
154154

155155
# ##############################################################################
156156
#

0 commit comments

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