-
Notifications
You must be signed in to change notification settings - Fork 53
/
aqo--1.2.sql
130 lines (115 loc) · 4.1 KB
/
aqo--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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION aqo" to load this file. \quit
CREATE TABLE public.aqo_queries (
query_hash bigint CONSTRAINT aqo_queries_query_hash_idx PRIMARY KEY,
learn_aqo boolean NOT NULL,
use_aqo boolean NOT NULL,
fspace_hash bigint NOT NULL,
auto_tuning boolean NOT NULL
);
CREATE TABLE public.aqo_query_texts (
query_hash bigint CONSTRAINT aqo_query_texts_query_hash_idx PRIMARY KEY REFERENCES public.aqo_queries ON DELETE CASCADE,
query_text text NOT NULL
);
CREATE TABLE public.aqo_query_stat (
query_hash bigint CONSTRAINT aqo_query_stat_idx PRIMARY KEY REFERENCES public.aqo_queries ON DELETE CASCADE,
execution_time_with_aqo double precision[],
execution_time_without_aqo double precision[],
planning_time_with_aqo double precision[],
planning_time_without_aqo double precision[],
cardinality_error_with_aqo double precision[],
cardinality_error_without_aqo double precision[],
executions_with_aqo bigint,
executions_without_aqo bigint
);
CREATE TABLE public.aqo_data (
fspace_hash bigint NOT NULL REFERENCES public.aqo_queries ON DELETE CASCADE,
fsspace_hash int NOT NULL,
nfeatures int NOT NULL,
features double precision[][],
targets double precision[]
);
CREATE UNIQUE INDEX aqo_fss_access_idx ON public.aqo_data (fspace_hash, fsspace_hash);
INSERT INTO public.aqo_queries VALUES (0, false, false, 0, false);
INSERT INTO public.aqo_query_texts VALUES (0, 'COMMON feature space (do not delete!)');
-- a virtual query for COMMON feature space
CREATE FUNCTION invalidate_deactivated_queries_cache() RETURNS trigger
AS 'MODULE_PATHNAME' LANGUAGE C;
CREATE TRIGGER aqo_queries_invalidate AFTER UPDATE OR DELETE OR TRUNCATE
ON public.aqo_queries FOR EACH STATEMENT
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();
--
-- Service functions
--
-- Show query state at the AQO knowledge base
CREATE FUNCTION public.aqo_status(hash bigint)
RETURNS TABLE (
"learn" BOOL,
"use aqo" BOOL,
"auto tune" BOOL,
"fspace hash" bigINT,
"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 FUNCTION public.aqo_enable_query(hash bigint)
RETURNS VOID
AS $func$
UPDATE public.aqo_queries SET
learn_aqo = 'true',
use_aqo = 'true'
WHERE query_hash = $1;
$func$ LANGUAGE SQL;
CREATE FUNCTION public.aqo_disable_query(hash bigint)
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 FUNCTION public.aqo_clear_hist(hash bigint)
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 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 FUNCTION public.aqo_drop(hash bigint)
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;