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

Commit031c0c8

Browse files
Alexandra Pervushinadanolivo
Alexandra Pervushina
authored andcommitted
Port remaining functions from stable13
Currently has problems with aqo_cleanup() function
1 parent1c3c088 commit031c0c8

File tree

69 files changed

+5762
-2193
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

69 files changed

+5762
-2193
lines changed

‎.github/workflows/c-cpp.yml

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,10 @@
1-
name:C/C++ CI for thestable14 branch.
1+
name:'C/C++ CI for thestable13'
22

33
on:
44
push:
5-
branches:[stable14 ]
5+
branches:[stable13 ]
66
pull_request:
7-
branches:[stable14 ]
7+
branches:[stable13 ]
88

99
jobs:
1010
build:
@@ -19,9 +19,9 @@ jobs:
1919
git config --global user.name "CI PgPro admin"
2020
git clone https://github.com/postgres/postgres.git pg
2121
cd pg
22-
git checkoutREL_14_STABLE
22+
git checkoutREL_13_STABLE
2323
./configure --prefix=`pwd`/tmp_install
2424
git subtree add --prefix=contrib/aqo https://github.com/postgrespro/aqo.git $GITHUB_REF
25-
patch -p1 --no-backup-if-mismatch < contrib/aqo/aqo_pg14.patch
25+
patch -p1 --no-backup-if-mismatch < contrib/aqo/aqo_pg13.patch
2626
make -j4 > /dev/null && make -j4 -C contrib > /dev/null
2727
env CLIENTS=50 THREADS=50 make -C contrib/aqo check

‎Makefile

Lines changed: 11 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,13 @@
11
# contrib/aqo/Makefile
22

33
EXTENSION = aqo
4-
EXTVERSION = 1.4
4+
EXTVERSION = 1.5
55
PGFILEDESC = "AQO - Adaptive Query Optimization"
66
MODULE_big = aqo
7-
OBJS = aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
8-
hash.o machine_learning.o path_utils.o postprocessing.o preprocessing.o\
9-
selectivity_cache.o storage.o utils.o learn_cache.o aqo_shared.o$(WIN32RES)
7+
OBJS =$(WIN32RES)\
8+
aqo.o auto_tuning.o cardinality_estimation.o cardinality_hooks.o\
9+
hash.o machine_learning.o path_utils.o postprocessing.o preprocessing.o\
10+
selectivity_cache.o storage.o utils.o learn_cache.o aqo_shared.o
1011

1112
TAP_TESTS = 1
1213

@@ -24,7 +25,11 @@ REGRESS =aqo_disabled \
2425
clean_aqo_data\
2526
plancache\
2627
statement_timeout\
27-
top_queries
28+
temp_tables\
29+
top_queries\
30+
relocatable\
31+
look_a_like\
32+
feature_subspace
2833

2934
fdw_srcdir =$(top_srcdir)/contrib/postgres_fdw
3035
stat_srcdir =$(top_srcdir)/contrib/pg_stat_statements
@@ -33,7 +38,7 @@ EXTRA_REGRESS_OPTS=--temp-config=$(top_srcdir)/$(subdir)/conf.add
3338
EXTRA_INSTALL = contrib/postgres_fdw contrib/pg_stat_statements
3439

3540
DATA = aqo--1.0.sql aqo--1.0--1.1.sql aqo--1.1--1.2.sql aqo--1.2.sql\
36-
aqo--1.2--1.3.sql aqo--1.3--1.4.sql
41+
aqo--1.2--1.3.sql aqo--1.3--1.4.sql aqo--1.4--1.5.sql
3742

3843
ifdefUSE_PGXS
3944
PG_CONFIG ?= pg_config

‎README.md

Lines changed: 3 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -13,9 +13,7 @@ To avoid compatibility issues, the following branches in the git-repository are
1313
*`stable9_6`.
1414
*`stable11` - for PG v10 and v11.
1515
*`stable12` - for PG v12.
16-
*`stable13` - for PG v13
17-
*`stable14` - for PG v14
18-
* the`master` branch of the AQO repository correctly works with PGv15 and the PostgreSQL`master` branch.
16+
* the`master` branch of the AQO repository correctly works with PGv13 and the PostgreSQL`master` branch.
1917

2018
The module contains a patch and an extension. Patch has to be applied to the
2119
sources of PostgresSQL. Patch affects header files, that is why PostgreSQL
@@ -214,7 +212,7 @@ execution of such query type. Disabling of AQO usage is reasonable for that
214212
cases in which query execution time increases after applying AQO. It happens
215213
sometimes because of cost models incompleteness.
216214

217-
`Fspace_hash` setting is for extra advanced AQO tuning. It may be changed manually
215+
`fs` setting is for extra advanced AQO tuning. It may be changed manually
218216
to optimize a number of queries using the same model. It may decrease the
219217
amount of memory for models and even the query execution time, but also it
220218
may cause the bad AQO's behavior, so please use it only if you know exactly
@@ -232,7 +230,7 @@ ignored.
232230

233231
If`aqo.mode` is`'learn'`, then the normalized query hash appends to aqo_queries
234232
with the default settings`learn_aqo=true`,`use_aqo=true`,`auto_tuning=false`, and
235-
`fspace_hash =query_hash` which means that AQO uses separate machine learning
233+
`fs =queryid` which means that AQO uses separate machine learning
236234
model for this query type optimization. After that the query is processed as if
237235
it already was in aqo_queries.
238236

‎aqo--1.4--1.5.sql

Lines changed: 160 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,160 @@
1+
/* contrib/aqo/aqo--1.4--1.5.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"ALTER EXTENSION aqo UPDATE TO '1.5'" to load this file. \quit
5+
6+
/* Remove old interface of the extension*/
7+
DROPFUNCTION array_mse;
8+
DROPFUNCTION array_avg;
9+
DROPFUNCTIONpublic.aqo_clear_hist;-- Should be renamed and reworked
10+
DROPFUNCTIONpublic.aqo_disable_query;
11+
DROPFUNCTIONpublic.aqo_drop;
12+
DROPFUNCTIONpublic.aqo_enable_query;
13+
DROPFUNCTIONpublic.aqo_ne_queries;-- Not needed anymore due to changing in the logic
14+
DROPFUNCTIONpublic.aqo_status;
15+
DROPFUNCTIONpublic.clean_aqo_data;
16+
DROPFUNCTIONpublic.show_cardinality_errors;
17+
DROPFUNCTIONpublic.top_time_queries;
18+
DROPTABLEpublic.aqo_data CASCADE;
19+
DROPTABLEpublic.aqo_queries CASCADE;
20+
DROPTABLEpublic.aqo_query_texts CASCADE;
21+
DROPTABLEpublic.aqo_query_stat CASCADE;
22+
23+
24+
/*
25+
* VIEWs to discover AQO data.
26+
*/
27+
CREATEFUNCTIONaqo_queries (
28+
OUT queryidbigint,
29+
OUT fsbigint,
30+
OUT learn_aqoboolean,
31+
OUT use_aqoboolean,
32+
OUT auto_tuningboolean
33+
)
34+
RETURNS SETOF record
35+
AS'MODULE_PATHNAME','aqo_queries'
36+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
37+
38+
CREATEFUNCTIONaqo_query_texts(OUT queryidbigint, OUT query_texttext)
39+
RETURNS SETOF record
40+
AS'MODULE_PATHNAME','aqo_query_texts'
41+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
42+
43+
CREATEFUNCTIONaqo_query_stat (
44+
OUT queryidbigint,
45+
OUT execution_time_with_aqodouble precision[],
46+
OUT execution_time_without_aqodouble precision[],
47+
OUT planning_time_with_aqodouble precision[],
48+
OUT planning_time_without_aqodouble precision[],
49+
OUT cardinality_error_with_aqodouble precision[],
50+
OUT cardinality_error_without_aqodouble precision[],
51+
OUT executions_with_aqobigint,
52+
OUT executions_without_aqobigint
53+
)
54+
RETURNS SETOF record
55+
AS'MODULE_PATHNAME','aqo_query_stat'
56+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
57+
58+
CREATEFUNCTIONaqo_data (
59+
OUT fsbigint,
60+
OUT fssinteger,
61+
OUT nfeaturesinteger,
62+
OUT featuresdouble precision[][],
63+
OUT targetsdouble precision[],
64+
OUT reliabilitydouble precision[],
65+
OUT oidsOid[]
66+
)
67+
RETURNS SETOF record
68+
AS'MODULE_PATHNAME','aqo_data'
69+
LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
70+
71+
CREATEVIEWaqo_query_statASSELECT*FROM aqo_query_stat();
72+
CREATEVIEWaqo_query_textsASSELECT*FROM aqo_query_texts();
73+
CREATEVIEWaqo_dataASSELECT*FROM aqo_data();
74+
CREATEVIEWaqo_queriesASSELECT*FROM aqo_queries();
75+
76+
/* UI functions*/
77+
78+
79+
CREATEFUNCTIONaqo_enable_query(queryidbigint)
80+
RETURNS void
81+
AS'MODULE_PATHNAME','aqo_enable_query'
82+
LANGUAGE C STRICT VOLATILE;
83+
84+
CREATEFUNCTIONaqo_disable_query(queryidbigint)
85+
RETURNS void
86+
AS'MODULE_PATHNAME','aqo_enable_query'
87+
LANGUAGE C STRICT VOLATILE;
88+
89+
CREATEFUNCTIONaqo_queries_update(
90+
queryidbigint, fsbigint, learn_aqo bool, use_aqo bool, auto_tuning bool)
91+
RETURNS bool
92+
AS'MODULE_PATHNAME','aqo_queries_update'
93+
LANGUAGE C VOLATILE;
94+
95+
--
96+
-- Get cardinality error of queries the last time they were executed.
97+
-- IN:
98+
-- controlled - show queries executed under a control of AQO (true);
99+
-- executed without an AQO control, but AQO has a stat on the query (false).
100+
--
101+
-- OUT:
102+
-- num - sequental number. Smaller number corresponds to higher error.
103+
-- id - ID of a query.
104+
-- fshash - feature space. Usually equal to zero or ID.
105+
-- error - AQO error that calculated on plan nodes of the query.
106+
-- nexecs - number of executions of queries associated with this ID.
107+
--
108+
CREATE OR REPLACEFUNCTIONaqo_cardinality_error(controlledboolean)
109+
RETURNS TABLE(numinteger, idbigint, fshashbigint, errordouble precision, nexecsbigint)
110+
AS'MODULE_PATHNAME','aqo_cardinality_error'
111+
LANGUAGE C STRICT VOLATILE;
112+
COMMENT ON FUNCTION aqo_cardinality_error(boolean) IS
113+
'Get cardinality error of queries the last time they were executed. Order queries according to an error value.';
114+
115+
--
116+
-- Show execution time of queries, for which AQO has statistics.
117+
-- controlled - show stat on executions where AQO was used for cardinality
118+
-- estimations, or not used (controlled = false).
119+
-- Last case is possible in disabled mode with aqo.force_collect_stat = 'on'.
120+
--
121+
CREATE OR REPLACEFUNCTIONaqo_execution_time(controlledboolean)
122+
RETURNS TABLE(numinteger, idbigint, fshashbigint, exec_timedouble precision, nexecsbigint)
123+
AS'MODULE_PATHNAME','aqo_execution_time'
124+
LANGUAGE C STRICT VOLATILE;
125+
COMMENT ON FUNCTION aqo_execution_time(boolean) IS
126+
'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.';
127+
128+
--
129+
-- Remove query class settings, text, statistics and ML data from AQO storage.
130+
-- Return number of FSS records, removed from the storage.
131+
--
132+
CREATE OR REPLACEFUNCTIONaqo_drop_class(queryidbigint)
133+
RETURNSinteger
134+
AS'MODULE_PATHNAME','aqo_drop_class'
135+
LANGUAGE C STRICT VOLATILE;
136+
COMMENT ON FUNCTION aqo_drop_class(bigint) IS
137+
'Remove info about an query class from AQO ML knowledge base.';
138+
139+
--
140+
-- Remove unneeded rows from the AQO ML storage.
141+
-- For common feature space, remove rows from aqo_data only.
142+
-- For custom feature space - remove all rows related to the space from all AQO
143+
-- tables even if only one oid for one feature subspace of the space is illegal.
144+
-- Returns number of deleted rows from aqo_queries and aqo_data tables.
145+
--
146+
CREATE OR REPLACEFUNCTIONaqo_cleanup(OUT nfsinteger, OUT nfssinteger)
147+
AS'MODULE_PATHNAME','aqo_cleanup'
148+
LANGUAGE C STRICT VOLATILE;
149+
COMMENT ON FUNCTION aqo_cleanup() IS
150+
'Remove unneeded rows from the AQO ML storage';
151+
152+
--
153+
-- Remove all records in the AQO storage.
154+
-- Return number of rows removed.
155+
--
156+
CREATEFUNCTIONaqo_reset() RETURNSbigint
157+
AS'MODULE_PATHNAME','aqo_reset'
158+
LANGUAGE C PARALLEL SAFE;
159+
COMMENT ON FUNCTION aqo_reset() IS
160+
'Reset all data gathered by AQO';

0 commit comments

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