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

Commitcb173a3

Browse files
committed
Add the show_cardinality_errors routine.
Add into AQO SQL interface one more function for an quick check of cardinalityerrors of last execution of each controlled query.
1 parent616d981 commitcb173a3

File tree

5 files changed

+104
-0
lines changed

5 files changed

+104
-0
lines changed

‎aqo--1.3--1.4.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,3 +4,32 @@
44
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.4'" to load this file. \quit
55

66
ALTERTABLEpublic.aqo_data ADD COLUMN reliabilitydouble precision [];
7+
8+
--
9+
-- Get IDs of queries having the largest cardinality error when last executed.
10+
-- 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.
13+
--
14+
CREATE OR REPLACEFUNCTIONpublic.show_cardinality_errors()
15+
RETURNS TABLE(numbigint, idbigint, error float)
16+
AS $$
17+
BEGIN
18+
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;
31+
END;
32+
$$ LANGUAGE plpgsql;
33+
34+
COMMENT ON FUNCTION public.show_cardinality_errors() IS
35+
'Get cardinality error of last query execution. Return queries having the largest error.';

‎expected/gucs.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,4 +28,11 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
2828
JOINS: 0
2929
(6 rows)
3030

31+
-- Check existence of the interface functions.
32+
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
33+
obj_description
34+
-----------------------------------------------------------------------------------------
35+
Get cardinality error of last query execution. Return queries having the largest error.
36+
(1 row)
37+
3138
DROP EXTENSION aqo;

‎expected/unsupported.out

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -538,5 +538,54 @@ EXPLAIN (COSTS OFF)
538538
JOINS: 0
539539
(9 rows)
540540

541+
-- XXX: Do we stuck into an unstable behavior of an error value?
542+
-- Live with this variant of the test for some time.
543+
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)
557+
541558
DROP TABLE t,t1 CASCADE;
559+
SELECT public.clean_aqo_data();
560+
NOTICE: Cleaning aqo_data records
561+
clean_aqo_data
562+
----------------
563+
564+
(1 row)
565+
566+
-- TODO: figure out with remaining queries in the ML storage.
567+
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
568+
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
569+
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)
590+
542591
DROP EXTENSION aqo;

‎sql/gucs.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,4 +11,7 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
1111
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
1212
SELECT xFROM t;
1313

14+
-- Check existence of the interface functions.
15+
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
16+
1417
DROP EXTENSION aqo;

‎sql/unsupported.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -154,5 +154,21 @@ ANALYZE t;
154154
EXPLAIN (COSTS OFF)
155155
SELECTcount(*)FROM tWHERE x<3AND mod(x,3)=1;
156156

157+
-- XXX: Do we stuck into an unstable behavior of an error value?
158+
-- Live with this variant of the test for some time.
159+
SELECT
160+
num,
161+
to_char(error,'9.99EEEE')::textAS error
162+
FROMpublic.show_cardinality_errors()
163+
WHERE error>0.;
164+
157165
DROPTABLE t,t1 CASCADE;
166+
167+
SELECTpublic.clean_aqo_data();
168+
169+
-- TODO: figure out with remaining queries in the ML storage.
170+
SELECT num, to_char(error,'9.99EEEE')::textAS error, query_text
171+
FROMpublic.show_cardinality_errors() cef, aqo_query_texts aqt
172+
WHEREaqt.query_hash=cef.id;
173+
158174
DROP EXTENSION aqo;

0 commit comments

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