Skip to content

Commit 93a614a

Browse files
a.pervushinadanolivo
a.pervushina
authored and
danolivo
committed
Add the show_cardinality_errors routine.
Add into AQO SQL interface one more function for an quick check of cardinality errors of last execution of each controlled query.
1 parent 8ca6d9c commit 93a614a

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
ALTER TABLE public.aqo_data ADD COLUMN reliability double 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 REPLACE FUNCTION public.show_cardinality_errors()
15+
RETURNS TABLE(num bigint, id bigint, 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_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;
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
@@ -32,4 +32,11 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
3232
JOINS: 0
3333
(6 rows)
3434

35+
-- Check existence of the interface functions.
36+
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.
40+
(1 row)
41+
3542
DROP EXTENSION aqo;

‎expected/unsupported.out

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -554,5 +554,54 @@ EXPLAIN (COSTS OFF)
554554
JOINS: 0
555555
(9 rows)
556556

557+
-- XXX: Do we stuck into an unstable behavior of an error value?
558+
-- Live with this variant of the test for some time.
559+
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)
573+
557574
DROP TABLE t,t1 CASCADE;
575+
SELECT public.clean_aqo_data();
576+
NOTICE: Cleaning aqo_data records
577+
clean_aqo_data
578+
----------------
579+
580+
(1 row)
581+
582+
-- TODO: figure out with remaining queries in the ML storage.
583+
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
584+
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
585+
WHERE aqt.query_hash = cef.id;
586+
num | error | query_text
587+
-----+-----------+-------------------------------------------------------------------------------------------
588+
1 | 9.69e+02 | SELECT str FROM expln(' +
589+
| | EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) +
590+
| | SELECT count(*) FROM +
591+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x = t.x)) AS q1 +
592+
| | JOIN +
593+
| | (SELECT * FROM t WHERE x % 3 < (SELECT avg(x) FROM t t0 WHERE t0.x <> t.x)) AS q2+
594+
| | ON q1.x = q2.x+1; +
595+
| | ') AS str WHERE str NOT LIKE '%Memory Usage%';
596+
2 | 3.27e+02 | SELECT +
597+
| | num, +
598+
| | to_char(error, '9.99EEEE')::text AS error +
599+
| | FROM public.show_cardinality_errors() +
600+
| | WHERE error > 0.;
601+
3 | 0.00e+00 | CREATE TABLE t AS SELECT (gs.* / 50) AS x FROM generate_series(1,1000) AS gs;
602+
4 | 0.00e+00 | SELECT public.clean_aqo_data();
603+
5 | 0.00e+00 | CREATE TABLE t1 AS SELECT mod(gs,10) AS x, mod(gs+1,10) AS y +
604+
| | FROM generate_series(1,1000) AS gs;
605+
(5 rows)
606+
558607
DROP EXTENSION aqo;

‎sql/gucs.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,4 +16,7 @@ EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
1616
EXPLAIN (ANALYZE, VERBOSE, COSTS OFF, TIMING OFF, SUMMARY OFF)
1717
SELECT x FROM t;
1818

19+
-- Check existence of the interface functions.
20+
SELECT obj_description('public.show_cardinality_errors'::regproc::oid);
21+
1922
DROP EXTENSION aqo;

‎sql/unsupported.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -172,5 +172,21 @@ ANALYZE t;
172172
EXPLAIN (COSTS OFF)
173173
SELECT count(*) FROM t WHERE x < 3 AND mod(x,3) = 1;
174174

175+
-- XXX: Do we stuck into an unstable behavior of an error value?
176+
-- Live with this variant of the test for some time.
177+
SELECT
178+
num,
179+
to_char(error, '9.99EEEE')::text AS error
180+
FROM public.show_cardinality_errors()
181+
WHERE error > 0.;
182+
175183
DROP TABLE t,t1 CASCADE;
184+
185+
SELECT public.clean_aqo_data();
186+
187+
-- TODO: figure out with remaining queries in the ML storage.
188+
SELECT num, to_char(error, '9.99EEEE')::text AS error, query_text
189+
FROM public.show_cardinality_errors() cef, aqo_query_texts aqt
190+
WHERE aqt.query_hash = cef.id;
191+
176192
DROP EXTENSION aqo;

0 commit comments

Comments
 (0)