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

Commit94bc12d

Browse files
committed
Add specific initial script for AQO 1.6.
It mostly caused by desire of reducing number of failures 001_pgbench.pl teston WINDOWS OSes (it is related to speed of file descriptor allocations inthe test, where we CREATE/DROP extensions competitively by several threads.Also, the aqo_CVE-2020-14350 test is corrected.
1 parent27f9b5b commit94bc12d

File tree

4 files changed

+282
-172
lines changed

4 files changed

+282
-172
lines changed

‎Makefile‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ EXTRA_INSTALL = contrib/postgres_fdw contrib/pg_stat_statements
3030

3131
DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql aqo--1.2.sql\
3232
aqo--1.2--1.3.sql aqo--1.3--1.4.sql aqo--1.4--1.5.sql\
33-
aqo--1.5--1.6.sql
33+
aqo--1.5--1.6.sql aqo--1.6.sql
3434

3535
ifdefUSE_PGXS
3636
PG_CONFIG ?= pg_config

‎aqo--1.6.sql‎

Lines changed: 210 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,210 @@
1+
/* contrib/aqo/aqo--1.6.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION aqo" to load this file. \quit
5+
6+
--
7+
-- Get cardinality error of queries the last time they were executed.
8+
-- IN:
9+
-- controlled - show queries executed under a control of AQO (true);
10+
-- executed without an AQO control, but AQO has a stat on the query (false).
11+
--
12+
-- OUT:
13+
-- num - sequental number. Smaller number corresponds to higher error.
14+
-- id - ID of a query.
15+
-- fshash - feature space. Usually equal to zero or ID.
16+
-- error - AQO error that calculated on plan nodes of the query.
17+
-- nexecs - number of executions of queries associated with this ID.
18+
--
19+
CREATEFUNCTIONaqo_cardinality_error(controlledboolean)
20+
RETURNS TABLE(numinteger, idbigint, fshashbigint, errordouble precision, nexecsbigint)
21+
AS'MODULE_PATHNAME','aqo_cardinality_error'
22+
LANGUAGE C STRICT VOLATILE;
23+
COMMENT ON FUNCTION aqo_cardinality_error(boolean) IS
24+
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';
25+
26+
--
27+
-- Remove unneeded rows from the AQO ML storage.
28+
-- For common feature space, remove rows from aqo_data only.
29+
-- For custom feature space - remove all rows related to the space from all AQO
30+
-- tables even if only one oid for one feature subspace of the space is illegal.
31+
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
32+
--
33+
CREATEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
34+
RETURNS record
35+
AS'MODULE_PATHNAME','aqo_cleanup'
36+
LANGUAGE C STRICT VOLATILE;
37+
COMMENT ON FUNCTION aqo_cleanup() IS
38+
'Remove unneeded rows from the AQO ML storage';
39+
40+
CREATEFUNCTIONaqo_disable_class(queryidbigint)
41+
RETURNS void
42+
AS'MODULE_PATHNAME','aqo_disable_query'
43+
LANGUAGE C STRICT VOLATILE;
44+
COMMENT ON FUNCTION aqo_disable_class(bigint) IS
45+
'Set learn_aqo, use_aqo and auto_tuning into false for a class of queries with specific queryid.';
46+
47+
--
48+
-- Remove query class settings, text, statistics and ML data from AQO storage.
49+
-- Return number of FSS records, removed from the storage.
50+
--
51+
CREATEFUNCTIONaqo_drop_class(queryidbigint)
52+
RETURNSinteger
53+
AS'MODULE_PATHNAME','aqo_drop_class'
54+
LANGUAGE C STRICT VOLATILE;
55+
COMMENT ON FUNCTION aqo_drop_class(bigint) IS
56+
'Remove info about an query class from AQO ML knowledge base.';
57+
58+
CREATEFUNCTIONaqo_enable_class(queryidbigint)
59+
RETURNS void
60+
AS'MODULE_PATHNAME','aqo_enable_query'
61+
LANGUAGE C STRICT VOLATILE;
62+
COMMENT ON FUNCTION aqo_enable_class(bigint) IS
63+
'Set learn_aqo, use_aqo and auto_tuning (in intelligent mode) into true for a class of queries with specific queryid.';
64+
65+
--
66+
-- Show execution time of queries, for which AQO has statistics.
67+
-- controlled - show stat on executions where AQO was used for cardinality
68+
-- estimations, or not used (controlled = false).
69+
-- Last case is possible in disabled mode with aqo.force_collect_stat = 'on'.
70+
--
71+
CREATEFUNCTIONaqo_execution_time(controlledboolean)
72+
RETURNS TABLE(numinteger, idbigint, fshashbigint, exec_timedouble precision, nexecsbigint)
73+
AS'MODULE_PATHNAME','aqo_execution_time'
74+
LANGUAGE C STRICT VOLATILE;
75+
COMMENT ON FUNCTION aqo_execution_time(boolean) IS
76+
'Get execution time of queries. If controlled = true (AQO could advise cardinality estimations), show time of last execution attempt. Another case (AQO not used), return an average value of execution time across all known executions.';
77+
78+
CREATEFUNCTIONaqo_memory_usage(
79+
OUT nametext,
80+
OUT allocated_sizeint,
81+
OUT used_sizeint
82+
)
83+
RETURNS SETOF record
84+
AS $$
85+
SELECT name, total_bytes, used_bytesFROM pg_backend_memory_contexts
86+
WHERE nameLIKE'AQO%'
87+
UNION
88+
SELECT name, allocated_size, sizeFROM pg_shmem_allocations
89+
WHERE nameLIKE'AQO%';
90+
$$ LANGUAGE SQL;
91+
COMMENT ON FUNCTION aqo_memory_usage() IS
92+
'Show allocated sizes and used sizes of aqo`s memory contexts and hash tables';
93+
94+
--
95+
-- Update or insert an aqo_data
96+
-- table record for given 'fs' & 'fss'.
97+
--
98+
99+
CREATEFUNCTIONaqo_data_update(
100+
fsbigint,
101+
fssinteger,
102+
nfeaturesinteger,
103+
featuresdouble precision[][],
104+
targetsdouble precision[],
105+
reliabilitydouble precision[],
106+
oidsOid[])
107+
RETURNS bool
108+
AS'MODULE_PATHNAME','aqo_data_update'
109+
LANGUAGE C VOLATILE;
110+
111+
CREATEFUNCTIONaqo_queries_update(
112+
queryidbigint, fsbigint, learn_aqo bool, use_aqo bool, auto_tuning bool)
113+
RETURNS bool
114+
AS'MODULE_PATHNAME','aqo_queries_update'
115+
LANGUAGE C VOLATILE;
116+
117+
--
118+
-- Update or insert an aqo_query_stat
119+
-- table record for given 'queryid'.
120+
--
121+
CREATEFUNCTIONaqo_query_stat_update(
122+
queryidbigint,
123+
execution_time_with_aqodouble precision[],
124+
execution_time_without_aqodouble precision[],
125+
planning_time_with_aqodouble precision[],
126+
planning_time_without_aqodouble precision[],
127+
cardinality_error_with_aqodouble precision[],
128+
cardinality_error_without_aqodouble precision[],
129+
executions_with_aqobigint,
130+
executions_without_aqobigint)
131+
RETURNS bool
132+
AS'MODULE_PATHNAME','aqo_query_stat_update'
133+
LANGUAGE C VOLATILE;
134+
135+
--
136+
-- Update or insert an aqo_query_texts
137+
-- table record for given 'queryid'.
138+
--
139+
CREATEFUNCTIONaqo_query_texts_update(
140+
queryidbigint, query_texttext)
141+
RETURNS bool
142+
AS'MODULE_PATHNAME','aqo_query_texts_update'
143+
LANGUAGE C VOLATILE;
144+
145+
--
146+
-- Remove all records in the AQO storage.
147+
-- Return number of rows removed.
148+
--
149+
CREATEFUNCTIONaqo_reset() RETURNSbigint
150+
AS'MODULE_PATHNAME','aqo_reset'
151+
LANGUAGE C PARALLEL SAFE;
152+
COMMENT ON FUNCTION aqo_reset() IS
153+
'Reset all data gathered by AQO';
154+
155+
-- -----------------------------------------------------------------------------
156+
--
157+
-- VIEWs
158+
--
159+
-- -----------------------------------------------------------------------------
160+
161+
CREATEFUNCTIONaqo_data (
162+
OUT fsbigint,
163+
OUT fssinteger,
164+
OUT nfeaturesinteger,
165+
OUT featuresdouble precision[][],
166+
OUT targetsdouble precision[],
167+
OUT reliabilitydouble precision[],
168+
OUT oidsOid[]
169+
)
170+
RETURNS SETOF record
171+
AS'MODULE_PATHNAME','aqo_data'
172+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
173+
174+
CREATEFUNCTIONaqo_queries (
175+
OUT queryidbigint,
176+
OUT fsbigint,
177+
OUT learn_aqoboolean,
178+
OUT use_aqoboolean,
179+
OUT auto_tuningboolean,
180+
OUT smart_timeoutbigint,
181+
OUT count_increase_timeoutbigint
182+
)
183+
RETURNS SETOF record
184+
AS'MODULE_PATHNAME','aqo_queries'
185+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
186+
187+
CREATEFUNCTIONaqo_query_stat (
188+
OUT queryidbigint,
189+
OUT execution_time_with_aqodouble precision[],
190+
OUT execution_time_without_aqodouble precision[],
191+
OUT planning_time_with_aqodouble precision[],
192+
OUT planning_time_without_aqodouble precision[],
193+
OUT cardinality_error_with_aqodouble precision[],
194+
OUT cardinality_error_without_aqodouble precision[],
195+
OUT executions_with_aqobigint,
196+
OUT executions_without_aqobigint
197+
)
198+
RETURNS SETOF record
199+
AS'MODULE_PATHNAME','aqo_query_stat'
200+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
201+
202+
CREATEFUNCTIONaqo_query_texts(OUT queryidbigint, OUT query_texttext)
203+
RETURNS SETOF record
204+
AS'MODULE_PATHNAME','aqo_query_texts'
205+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
206+
207+
CREATEVIEWaqo_dataASSELECT*FROM aqo_data();
208+
CREATEVIEWaqo_queriesASSELECT*FROM aqo_queries();
209+
CREATEVIEWaqo_query_statASSELECT*FROM aqo_query_stat();
210+
CREATEVIEWaqo_query_textsASSELECT*FROM aqo_query_texts();

0 commit comments

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