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

Commita77879e

Browse files
committed
Correctly report lock wait time in case of reenabling deadlock_timeout
0 parents  commita77879e

26 files changed

+5694
-0
lines changed

‎Makefile

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
# contrib/aqo/Makefile
2+
3+
EXTENSION = aqo
4+
PGFILEDESC = "AQO - adaptive query optimization"
5+
MODULES = aqo
6+
DATA = aqo--1.0.sql
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$(WIN32RES)
10+
REGRESS = aqo_disabled aqo_controlled aqo_intelligent aqo_forced
11+
12+
MODULE_big = aqo
13+
ifdefUSE_PGXS
14+
PG_CONFIG = pg_config
15+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
16+
include$(PGXS)
17+
else
18+
subdir = contrib/aqo
19+
top_builddir = ../..
20+
include$(top_builddir)/src/Makefile.global
21+
include$(top_srcdir)/contrib/contrib-global.mk
22+
endif

‎README.md

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
#Adaptive query optimization
2+
3+
Adaptive query optimization is the extension of standard PostgreSQL cost-based
4+
query optimizer. Its basic principle is to use query execution statistics
5+
for improving cardinality estimation. Experimental evaluation shows that this
6+
improvement sometimes provides an enormously large speed-up for rather
7+
complicated queries.
8+
9+
##Installation
10+
11+
In your db:
12+
CREATE EXTENSION aqo;
13+
14+
and modify your postgresql.conf:
15+
shared_preload_libraries = 'aqo.so'
16+
17+
It is essential that library is preloaded during server startup, because
18+
adaptive query optimization has to be enabled on per-database basis instead
19+
of per-connection.
20+
21+
##Usage
22+
23+
Note that the extension works bad with dynamically generated views. If they
24+
appear in workload, please use "aqo.mode='controlled'".
25+
26+
This extension has intelligent self-tuning mode. If you want to rely completely
27+
on it, just add line "aqo.mode = 'intelligent'" into your postgresql.conf.
28+
29+
Now this mode may work not good for rapidly changing data and query
30+
distributions, so it is better to reset extension manually when that happens.
31+
32+
Also please note that intelligent mode is not supposed to work with queries
33+
with dynamically generated structure. Dynamically generated constants are okay.
34+
35+
For handling workloads with dynamically generated query structures the forced
36+
mode "aqo.mode = 'forced'" is provided. We cannot guarantee performance
37+
improvement with this mode, but you may try it nevertheless.
38+
39+
If you want to completely control how PostgreSQL optimizes queries, use controlled
40+
mode "aqo.mode = 'controlled'" and
41+
contrib/aqo/learn_queries.sh file_with_sql_queries.sql "psql -d YOUR_DATABASE"
42+
where file_with_sql_queries.sql is a textfile with queries on which AQO is
43+
supposed to learn. Please use only SELECT queries file_with_sql_queries.sql.
44+
More sophisticated and convenient tool for AQO administration is in the
45+
development now.
46+
If you want to freeze optimizer's behavior (i. e. disable learning under
47+
workload), use "UPDATE aqo_queries SET auto_tuning=false;".
48+
If you want to disable AQO for all queries, you may use
49+
"UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;".
50+
51+
##Advanced tuning
52+
53+
To control query optimization we introduce for each query its type.
54+
We consider that queries belong to the same type if and only if they differ only
55+
in their constants.
56+
One can see an example of query corresponding to the specified query type
57+
in table aqo_query_texts.
58+
select * from aqo_query_texts;
59+
60+
That is why intelligent mode does not work for dynamically generated query
61+
structures: it tries to learn separately how to optimize different query types,
62+
and for dynamical query structure the query types are different, so it will
63+
consume a lot of memory and will not optimize any query properly.
64+
65+
Forced mode forces AQO to ignore query types and optimize them together. On one
66+
hand it lacks of intelligent tuning, so the performance for some queries may
67+
even decrease, on the other hand it may work for dynamic workload and consumes
68+
less memory than the intelligent mode. That is why you may want to use it.
69+
70+
Each query type has its own optimization settings. You can find them in table
71+
aqo_queries.
72+
73+
Auto_tuning setting identifies whether AQO module tries to tune other settings
74+
from aqo_queries for the query type on its own. If the mode is intelligent,
75+
default value for new queries is true. If the mode is not intelligent, new queries
76+
are not appended to aqo_queries automatically, but you can also set auto_tuning
77+
variable to true manually.
78+
79+
Use_aqo setting shows whether AQO cardinalities prediction be used for next
80+
execution of such query type. Disabling of AQO usage is reasonable for that
81+
cases in which query execution time increases after applying AQO. It happens
82+
sometimes because of cost models incompleteness.
83+
84+
Learn_aqo setting shows whether AQO collects statistics for next execution of
85+
such query type. True value may have computational overheads, but it is
86+
essential when AQO model does not fit the data. It happens at the start of AQO
87+
for the new query type or when the data distribution in database is changed.
88+
89+
Fspace_hash setting is for extra advanced AQO tuning. It may be changed manually
90+
to optimize a number of query types using the same model. It may decrease the
91+
amount of memory for models and even query execution performance, but also it
92+
may cause the bad AQO's behavior, so please use it only if you know exactly
93+
what you do.
94+
95+
##Statistics
96+
97+
For forced and intelligent query modes, and for all tracked queries the
98+
statistics is collected. The statistics is cardinality quality, planning and
99+
execution time. For forced mode the statistics for all untracked query types
100+
is stored in common query type with hash 0.
101+
102+
One can see the collected statistics in table aqo_query_stat.

‎aqo--1.0.sql

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
2+
\echo Use"CREATE EXTENSION aqo" to load this file. \quit
3+
4+
CREATETABLEaqo_queries (
5+
query_hashintPRIMARY KEY,
6+
learn_aqobooleanNOT NULL,
7+
use_aqobooleanNOT NULL,
8+
fspace_hashintNOT NULL,
9+
auto_tuningbooleanNOT NULL
10+
);
11+
12+
CREATETABLEaqo_query_texts (
13+
query_hashintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
14+
query_textvarcharNOT NULL
15+
);
16+
17+
CREATETABLEaqo_query_stat (
18+
query_hashintPRIMARY KEYREFERENCES aqo_queriesON DELETE CASCADE,
19+
execution_time_with_aqodouble precision[],
20+
execution_time_without_aqodouble precision[],
21+
planning_time_with_aqodouble precision[],
22+
planning_time_without_aqodouble precision[],
23+
cardinality_error_with_aqodouble precision[],
24+
cardinality_error_without_aqodouble precision[],
25+
executions_with_aqobigint,
26+
executions_without_aqobigint
27+
);
28+
29+
CREATETABLEaqo_data (
30+
fspace_hashintNOT NULLREFERENCES aqo_queriesON DELETE CASCADE,
31+
fsspace_hashintNOT NULL,
32+
nfeaturesintNOT NULL,
33+
featuresdouble precision[][],
34+
targetsdouble precision[],
35+
UNIQUE (fspace_hash, fsspace_hash)
36+
);
37+
38+
CREATEINDEXaqo_queries_query_hash_idxON aqo_queries (query_hash);
39+
CREATEINDEXaqo_query_texts_query_hash_idxON aqo_query_texts (query_hash);
40+
CREATEINDEXaqo_query_stat_idxON aqo_query_stat (query_hash);
41+
CREATEINDEXaqo_fss_access_idxON aqo_data (fspace_hash, fsspace_hash);
42+
43+
ALTERTABLE aqo_data ALTER COLUMN featuresSET STORAGE MAIN;
44+
ALTERTABLE aqo_data ALTER COLUMN targetsSET STORAGE MAIN;
45+
ALTERTABLE aqo_query_stat
46+
ALTER COLUMN execution_time_with_aqoSET STORAGE MAIN;
47+
ALTERTABLE aqo_query_stat
48+
ALTER COLUMN execution_time_without_aqoSET STORAGE MAIN;
49+
ALTERTABLE aqo_query_stat
50+
ALTER COLUMN planning_time_with_aqoSET STORAGE MAIN;
51+
ALTERTABLE aqo_query_stat
52+
ALTER COLUMN planning_time_without_aqoSET STORAGE MAIN;
53+
ALTERTABLE aqo_query_stat
54+
ALTER COLUMN cardinality_error_without_aqoSET STORAGE MAIN;
55+
ALTERTABLE aqo_query_stat
56+
ALTER COLUMN cardinality_error_with_aqoSET STORAGE MAIN;
57+
58+
INSERT INTO aqo_queriesVALUES (0, false, false,0, false);
59+
INSERT INTO aqo_query_textsVALUES (0,'COMMON feature space (do not delete!)');
60+
-- a virtual query for COMMON feature space
61+
62+
CREATEFUNCTIONinvalidate_deactivated_queries_cache() RETURNS trigger
63+
AS'MODULE_PATHNAME' LANGUAGE C;
64+
65+
CREATETRIGGERaqo_queries_invalidate AFTERUPDATEORDELETEOR TRUNCATE
66+
ON aqo_queries FOR EACH STATEMENT
67+
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();

‎aqo.c

Lines changed: 142 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,142 @@
1+
#include"aqo.h"
2+
3+
PG_MODULE_MAGIC;
4+
5+
void_PG_init(void);
6+
void_PG_fini(void);
7+
8+
/* Strategy of determining feature space for new queries. */
9+
intaqo_mode;
10+
11+
/* GUC variables */
12+
staticconststructconfig_enum_entryformat_options[]= {
13+
{"intelligent",AQO_MODE_INTELLIGENT, false},
14+
{"forced",AQO_MODE_FORCED, false},
15+
{"controlled",AQO_MODE_CONTROLLED, false},
16+
{"disabled",AQO_MODE_DISABLED, false},
17+
{NULL,0, false}
18+
};
19+
20+
/* Parameters of autotuning */
21+
intaqo_stat_size=20;
22+
intauto_tuning_window_size=5;
23+
doubleauto_tuning_exploration=0.1;
24+
intauto_tuning_max_iterations=50;
25+
intauto_tuning_infinite_loop=8;
26+
27+
/* stat_size > infinite_loop + window_size + 3 is required for auto_tuning*/
28+
29+
/* Machine learning parameters */
30+
doubleobject_selection_prediction_threshold=0.3;
31+
doubleobject_selection_object_threshold=0.1;
32+
doublelearning_rate=1e-1;
33+
intaqo_k=3;
34+
intaqo_K=30;
35+
doublelog_selectivity_lower_bound=-30;
36+
37+
/* Parameters for current query */
38+
intquery_hash;
39+
boollearn_aqo;
40+
booluse_aqo;
41+
intfspace_hash;
42+
boolauto_tuning;
43+
boolcollect_stat;
44+
booladding_query;
45+
boolexplain_only;
46+
boolexplain_aqo;
47+
48+
/* Query execution time */
49+
instr_timequery_starttime;
50+
doublequery_planning_time;
51+
52+
/* Saved hook values in case of unload */
53+
post_parse_analyze_hook_typeprev_post_parse_analyze_hook;
54+
planner_hook_typeprev_planner_hook;
55+
ExecutorStart_hook_typeprev_ExecutorStart_hook;
56+
ExecutorEnd_hook_typeprev_ExecutorEnd_hook;
57+
set_baserel_rows_estimate_hook_typeprev_set_baserel_rows_estimate_hook;
58+
get_parameterized_baserel_size_hook_typeprev_get_parameterized_baserel_size_hook;
59+
set_joinrel_size_estimates_hook_typeprev_set_joinrel_size_estimates_hook;
60+
get_parameterized_joinrel_size_hook_typeprev_get_parameterized_joinrel_size_hook;
61+
copy_generic_path_info_hook_typeprev_copy_generic_path_info_hook;
62+
ExplainOnePlan_hook_typeprev_ExplainOnePlan_hook;
63+
64+
/*****************************************************************************
65+
*
66+
*CREATE/DROP EXTENSION FUNCTIONS
67+
*
68+
*****************************************************************************/
69+
70+
void
71+
_PG_init(void)
72+
{
73+
DefineCustomEnumVariable("aqo.mode",
74+
"Mode of aqo usage.",
75+
NULL,
76+
&aqo_mode,
77+
AQO_MODE_CONTROLLED,
78+
format_options,
79+
PGC_SUSET,
80+
0,
81+
NULL,
82+
NULL,
83+
NULL);
84+
85+
prev_planner_hook=planner_hook;
86+
planner_hook=&aqo_planner;
87+
prev_post_parse_analyze_hook=post_parse_analyze_hook;
88+
post_parse_analyze_hook=&get_query_text;
89+
prev_ExecutorStart_hook=ExecutorStart_hook;
90+
ExecutorStart_hook=&aqo_ExecutorStart;
91+
prev_ExecutorEnd_hook=ExecutorEnd_hook;
92+
ExecutorEnd_hook=&learn_query_stat;
93+
prev_set_baserel_rows_estimate_hook=set_baserel_rows_estimate_hook;
94+
set_baserel_rows_estimate_hook=&aqo_set_baserel_rows_estimate;
95+
prev_get_parameterized_baserel_size_hook=
96+
get_parameterized_baserel_size_hook;
97+
get_parameterized_baserel_size_hook=
98+
&aqo_get_parameterized_baserel_size;
99+
prev_set_joinrel_size_estimates_hook=set_joinrel_size_estimates_hook;
100+
set_joinrel_size_estimates_hook=&aqo_set_joinrel_size_estimates;
101+
prev_get_parameterized_joinrel_size_hook=
102+
get_parameterized_joinrel_size_hook;
103+
get_parameterized_joinrel_size_hook=
104+
&aqo_get_parameterized_joinrel_size;
105+
prev_copy_generic_path_info_hook=copy_generic_path_info_hook;
106+
copy_generic_path_info_hook=&aqo_copy_generic_path_info;
107+
prev_ExplainOnePlan_hook=ExplainOnePlan_hook;
108+
ExplainOnePlan_hook=print_into_explain;
109+
init_deactivated_queries_storage();
110+
}
111+
112+
void
113+
_PG_fini(void)
114+
{
115+
planner_hook=prev_planner_hook;
116+
post_parse_analyze_hook=prev_post_parse_analyze_hook;
117+
ExecutorStart_hook=prev_ExecutorStart_hook;
118+
ExecutorEnd_hook=prev_ExecutorEnd_hook;
119+
set_baserel_rows_estimate_hook=prev_set_baserel_rows_estimate_hook;
120+
get_parameterized_baserel_size_hook=
121+
prev_get_parameterized_baserel_size_hook;
122+
set_joinrel_size_estimates_hook=prev_set_joinrel_size_estimates_hook;
123+
get_parameterized_joinrel_size_hook=
124+
prev_get_parameterized_joinrel_size_hook;
125+
copy_generic_path_info_hook=prev_copy_generic_path_info_hook;
126+
ExplainOnePlan_hook=prev_ExplainOnePlan_hook;
127+
fini_deactivated_queries_storage();
128+
}
129+
130+
PG_FUNCTION_INFO_V1(invalidate_deactivated_queries_cache);
131+
132+
/*
133+
* Clears the cache of deactivated queries if the user changed aqo_queries
134+
* manually.
135+
*/
136+
Datum
137+
invalidate_deactivated_queries_cache(PG_FUNCTION_ARGS)
138+
{
139+
fini_deactivated_queries_storage();
140+
init_deactivated_queries_storage();
141+
PG_RETURN_POINTER(NULL);
142+
}

‎aqo.control

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,5 @@
1+
# AQO extension
2+
comment = 'machine learning for cardinality estimation in optimizer'
3+
default_version = '1.0'
4+
module_pathname = '$libdir/aqo'
5+
relocatable = false

0 commit comments

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