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

Commitf0727fb

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 parent44a5853 commitf0727fb

File tree

8 files changed

+137
-89
lines changed

8 files changed

+137
-89
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: 9 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -543,17 +543,16 @@ EXPLAIN (COSTS OFF)
543543
SELECT
544544
num,
545545
to_char(error, '9.99EEEE')::text AS error
546-
FROM public.show_cardinality_errors()
546+
FROM public.show_cardinality_errors(true)
547547
WHERE error > 0.;
548548
num | error
549549
-----+-----------
550-
1 |9.69e+02
551-
2 |1.15e+02
550+
1 |1.15e+02
551+
2 |3.00e+01
552552
3 | 3.00e+01
553553
4 | 3.00e+01
554-
5 | 3.00e+01
555-
6 | 1.33e+00
556-
(6 rows)
554+
5 | 1.33e+00
555+
(5 rows)
557556

558557
DROP TABLE t,t1 CASCADE;
559558
SELECT public.clean_aqo_data();
@@ -565,27 +564,10 @@ NOTICE: Cleaning aqo_data records
565564

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

591573
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
staticbool
413413
isQueryUsingSystemRelation(Query*query)
414414
{
415-
returnisQueryUsingSystemRelation_walker((Node*)query,NULL);
415+
booltrivQuery= true;
416+
boolresult;
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
Relationrel=table_open(rte->relid,AccessShareLock);
452459
boolis_catalog=IsCatalogRelation(rel);
453460
boolis_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
elseif (rte->rtekind==RTE_FUNCTION)
460470
{

‎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: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -159,7 +159,7 @@ EXPLAIN (COSTS OFF)
159159
SELECT
160160
num,
161161
to_char(error,'9.99EEEE')::textAS error
162-
FROMpublic.show_cardinality_errors()
162+
FROMpublic.show_cardinality_errors(true)
163163
WHERE error>0.;
164164

165165
DROPTABLE t,t1 CASCADE;
@@ -168,7 +168,7 @@ SELECT public.clean_aqo_data();
168168

169169
-- TODO: figure out with 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

174174
DROP EXTENSION aqo;

‎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(*) FROMtop_error_queries(10) v
138-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
137+
"SELECT count(*) FROMshow_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 * FROMtop_error_queries(10) v
143-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
142+
"SELECT * FROMshow_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 FROMtop_error_queries(10) v
148-
JOIN aqo_query_texts t ON (t.query_hash = v.fspace_hash)
147+
"SELECT v.error, t.query_text FROMshow_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)
[8]ページ先頭
©2009-2025 Movatter.jp