forked from postgrespro/aqo
-
Notifications
You must be signed in to change notification settings - Fork 0
/
aqo--1.1--1.2.sql
106 lines (95 loc) · 2.81 KB
/
aqo--1.1--1.2.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
CREATE OR REPLACE FUNCTION aqo_migrate_to_1_2_get_pk(relid regclass) RETURNS text AS $$
DECLARE
res text;
BEGIN
SELECT conname
FROM pg_catalog.pg_constraint
WHERE conrelid = relid AND contype = 'u'
INTO res;
RETURN res;
END
$$ LANGUAGE plpgsql;
DO $$
BEGIN
EXECUTE pg_catalog.format(
'ALTER TABLE public.aqo_data DROP CONSTRAINT %s',
aqo_migrate_to_1_2_get_pk('public.aqo_data'::regclass),
'aqo_queries_query_hash_idx');
END
$$;
DROP FUNCTION aqo_migrate_to_1_2_get_pk(regclass);
--
-- Service functions
--
-- Show query state at the AQO knowledge base
CREATE OR REPLACE FUNCTION public.aqo_status(hash int)
RETURNS TABLE (
"learn" BOOL,
"use aqo" BOOL,
"auto tune" BOOL,
"fspace hash" INT,
"t_naqo" TEXT,
"err_naqo" TEXT,
"iters" BIGINT,
"t_aqo" TEXT,
"err_aqo" TEXT,
"iters_aqo" BIGINT
)
AS $func$
SELECT learn_aqo,use_aqo,auto_tuning,fspace_hash,
to_char(execution_time_without_aqo[n4],'9.99EEEE'),
to_char(cardinality_error_without_aqo[n2],'9.99EEEE'),
executions_without_aqo,
to_char(execution_time_with_aqo[n3],'9.99EEEE'),
to_char(cardinality_error_with_aqo[n1],'9.99EEEE'),
executions_with_aqo
FROM public.aqo_queries aq, public.aqo_query_stat aqs,
(SELECT array_length(n1,1) AS n1, array_length(n2,1) AS n2,
array_length(n3,1) AS n3, array_length(n4,1) AS n4
FROM
(SELECT cardinality_error_with_aqo AS n1,
cardinality_error_without_aqo AS n2,
execution_time_with_aqo AS n3,
execution_time_without_aqo AS n4
FROM public.aqo_query_stat aqs WHERE
aqs.query_hash = $1) AS al) AS q
WHERE (aqs.query_hash = aq.query_hash) AND
aqs.query_hash = $1;
$func$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION public.aqo_enable_query(hash int)
RETURNS VOID
AS $func$
UPDATE public.aqo_queries SET
learn_aqo = 'true',
use_aqo = 'true'
WHERE query_hash = $1;
$func$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION public.aqo_disable_query(hash int)
RETURNS VOID
AS $func$
UPDATE public.aqo_queries SET
learn_aqo = 'false',
use_aqo = 'false',
auto_tuning = 'false'
WHERE query_hash = $1;
$func$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION public.aqo_clear_hist(hash int)
RETURNS VOID
AS $func$
DELETE FROM public.aqo_data WHERE fspace_hash=$1;
$func$ LANGUAGE SQL;
-- Show queries that contains 'Never executed' nodes at the plan.
CREATE OR REPLACE FUNCTION public.aqo_ne_queries()
RETURNS SETOF int
AS $func$
SELECT query_hash FROM public.aqo_query_stat aqs
WHERE -1 = ANY (cardinality_error_with_aqo::double precision[]);
$func$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION public.aqo_drop(hash int)
RETURNS VOID
AS $func$
DELETE FROM public.aqo_queries aq WHERE (aq.query_hash = $1);
DELETE FROM public.aqo_data ad WHERE (ad.fspace_hash = $1);
DELETE FROM public.aqo_query_stat aq WHERE (aq.query_hash = $1);
DELETE FROM public.aqo_query_texts aq WHERE (aq.query_hash = $1);
$func$ LANGUAGE SQL;