Skip to content

Commit 32d2001

Browse files
vbwagnervbwagner
committed
Merge branch 'PGPROEE9_6_aqo' into PGPROEE9_6
2 parents fed1ec6 + 4f5e513 commit 32d2001

33 files changed

+5241
-59
lines changed

‎contrib/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ SUBDIRS = \
88
adminpack \
99
auth_delay \
1010
auto_explain \
11+
aqo \
1112
bloom \
1213
btree_gin \
1314
btree_gist \

‎contrib/aqo/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_manual aqo_intelligent aqo_forced
11+
12+
MODULE_big = aqo
13+
ifdef USE_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

‎contrib/aqo/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='manual'".
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 manual
40+
mode "aqo.mode = 'manual'" 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.

‎contrib/aqo/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+
CREATE TABLE aqo_queries (
5+
query_hash int PRIMARY KEY,
6+
learn_aqo boolean NOT NULL,
7+
use_aqo boolean NOT NULL,
8+
fspace_hash int NOT NULL,
9+
auto_tuning boolean NOT NULL
10+
);
11+
12+
CREATE TABLE aqo_query_texts (
13+
query_hash int PRIMARY KEY REFERENCES aqo_queries ON DELETE CASCADE,
14+
query_text varchar NOT NULL
15+
);
16+
17+
CREATE TABLE aqo_query_stat (
18+
query_hash int PRIMARY KEY REFERENCES aqo_queries ON DELETE CASCADE,
19+
execution_time_with_aqo double precision[],
20+
execution_time_without_aqo double precision[],
21+
planning_time_with_aqo double precision[],
22+
planning_time_without_aqo double precision[],
23+
cardinality_error_with_aqo double precision[],
24+
cardinality_error_without_aqo double precision[],
25+
executions_with_aqo bigint,
26+
executions_without_aqo bigint
27+
);
28+
29+
CREATE TABLE aqo_data (
30+
fspace_hash int NOT NULL REFERENCES aqo_queries ON DELETE CASCADE,
31+
fsspace_hash int NOT NULL,
32+
nfeatures int NOT NULL,
33+
features double precision[][],
34+
targets double precision[],
35+
UNIQUE (fspace_hash, fsspace_hash)
36+
);
37+
38+
CREATE INDEX aqo_queries_query_hash_idx ON aqo_queries (query_hash);
39+
CREATE INDEX aqo_query_texts_query_hash_idx ON aqo_query_texts (query_hash);
40+
CREATE INDEX aqo_query_stat_idx ON aqo_query_stat (query_hash);
41+
CREATE INDEX aqo_fss_access_idx ON aqo_data (fspace_hash, fsspace_hash);
42+
43+
ALTER TABLE aqo_data ALTER COLUMN features SET STORAGE MAIN;
44+
ALTER TABLE aqo_data ALTER COLUMN targets SET STORAGE MAIN;
45+
ALTER TABLE aqo_query_stat
46+
ALTER COLUMN execution_time_with_aqo SET STORAGE MAIN;
47+
ALTER TABLE aqo_query_stat
48+
ALTER COLUMN execution_time_without_aqo SET STORAGE MAIN;
49+
ALTER TABLE aqo_query_stat
50+
ALTER COLUMN planning_time_with_aqo SET STORAGE MAIN;
51+
ALTER TABLE aqo_query_stat
52+
ALTER COLUMN planning_time_without_aqo SET STORAGE MAIN;
53+
ALTER TABLE aqo_query_stat
54+
ALTER COLUMN cardinality_error_without_aqo SET STORAGE MAIN;
55+
ALTER TABLE aqo_query_stat
56+
ALTER COLUMN cardinality_error_with_aqo SET STORAGE MAIN;
57+
58+
INSERT INTO aqo_queries VALUES (0, false, false, 0, false);
59+
INSERT INTO aqo_query_texts VALUES (0, 'COMMON feature space (do not delete!)');
60+
-- a virtual query for COMMON feature space
61+
62+
CREATE FUNCTION invalidate_deactivated_queries_cache() RETURNS trigger
63+
AS 'MODULE_PATHNAME' LANGUAGE C;
64+
65+
CREATE TRIGGER aqo_queries_invalidate AFTER UPDATE OR DELETE OR TRUNCATE
66+
ON aqo_queries FOR EACH STATEMENT
67+
EXECUTE PROCEDURE invalidate_deactivated_queries_cache();

‎contrib/aqo/aqo.c

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,136 @@
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+
int aqo_mode;
10+
11+
/* GUC variables */
12+
static const struct config_enum_entry format_options[] = {
13+
{"intelligent", AQO_MODE_INTELLIGENT, false},
14+
{"forced", AQO_MODE_FORCED, false},
15+
{"manual", AQO_MODE_MANUAL, false},
16+
{NULL, 0, false}
17+
};
18+
19+
/* Parameters of autotuning */
20+
int aqo_stat_size = 20;
21+
int auto_tuning_window_size = 5;
22+
double auto_tuning_exploration = 0.1;
23+
int auto_tuning_max_iterations = 50;
24+
int auto_tuning_infinite_loop = 8;
25+
26+
/* stat_size > infinite_loop + window_size + 3 is required for auto_tuning*/
27+
28+
/* Machine learning parameters */
29+
double object_selection_prediction_threshold = 0.3;
30+
double object_selection_object_threshold = 0.1;
31+
double learning_rate = 1e-1;
32+
int aqo_k = 3;
33+
int aqo_K = 30;
34+
double log_selectivity_lower_bound = -30;
35+
36+
/* Parameters for current query */
37+
int query_hash;
38+
bool learn_aqo;
39+
bool use_aqo;
40+
int fspace_hash;
41+
bool auto_tuning;
42+
bool collect_stat;
43+
bool adding_query;
44+
bool explain_only;
45+
46+
/* Query execution time */
47+
instr_time query_starttime;
48+
double query_planning_time;
49+
50+
/* Saved hook values in case of unload */
51+
post_parse_analyze_hook_type prev_post_parse_analyze_hook;
52+
planner_hook_type prev_planner_hook;
53+
ExecutorStart_hook_type prev_ExecutorStart_hook;
54+
ExecutorEnd_hook_type prev_ExecutorEnd_hook;
55+
set_baserel_rows_estimate_hook_type prev_set_baserel_rows_estimate_hook;
56+
get_parameterized_baserel_size_hook_type prev_get_parameterized_baserel_size_hook;
57+
set_joinrel_size_estimates_hook_type prev_set_joinrel_size_estimates_hook;
58+
get_parameterized_joinrel_size_hook_type prev_get_parameterized_joinrel_size_hook;
59+
copy_generic_path_info_hook_type prev_copy_generic_path_info_hook;
60+
61+
/*****************************************************************************
62+
*
63+
* CREATE/DROP EXTENSION FUNCTIONS
64+
*
65+
*****************************************************************************/
66+
67+
void
68+
_PG_init(void)
69+
{
70+
DefineCustomEnumVariable("aqo.mode",
71+
"Mode of aqo usage.",
72+
NULL,
73+
&aqo_mode,
74+
AQO_MODE_MANUAL,
75+
format_options,
76+
PGC_SUSET,
77+
0,
78+
NULL,
79+
NULL,
80+
NULL);
81+
82+
prev_planner_hook = planner_hook;
83+
planner_hook = &aqo_planner;
84+
prev_post_parse_analyze_hook = post_parse_analyze_hook;
85+
post_parse_analyze_hook = &get_query_text;
86+
prev_ExecutorStart_hook = ExecutorStart_hook;
87+
ExecutorStart_hook = &aqo_ExecutorStart;
88+
prev_ExecutorEnd_hook = ExecutorEnd_hook;
89+
ExecutorEnd_hook = &learn_query_stat;
90+
prev_set_baserel_rows_estimate_hook = set_baserel_rows_estimate_hook;
91+
set_baserel_rows_estimate_hook = &aqo_set_baserel_rows_estimate;
92+
prev_get_parameterized_baserel_size_hook =
93+
get_parameterized_baserel_size_hook;
94+
get_parameterized_baserel_size_hook =
95+
&aqo_get_parameterized_baserel_size;
96+
prev_set_joinrel_size_estimates_hook = set_joinrel_size_estimates_hook;
97+
set_joinrel_size_estimates_hook = &aqo_set_joinrel_size_estimates;
98+
prev_get_parameterized_joinrel_size_hook =
99+
get_parameterized_joinrel_size_hook;
100+
get_parameterized_joinrel_size_hook =
101+
&aqo_get_parameterized_joinrel_size;
102+
prev_copy_generic_path_info_hook = copy_generic_path_info_hook;
103+
copy_generic_path_info_hook = &aqo_copy_generic_path_info;
104+
init_deactivated_queries_storage();
105+
}
106+
107+
void
108+
_PG_fini(void)
109+
{
110+
planner_hook = prev_planner_hook;
111+
post_parse_analyze_hook = prev_post_parse_analyze_hook;
112+
ExecutorStart_hook = prev_ExecutorStart_hook;
113+
ExecutorEnd_hook = prev_ExecutorEnd_hook;
114+
set_baserel_rows_estimate_hook = prev_set_baserel_rows_estimate_hook;
115+
get_parameterized_baserel_size_hook =
116+
prev_get_parameterized_baserel_size_hook;
117+
set_joinrel_size_estimates_hook = prev_set_joinrel_size_estimates_hook;
118+
get_parameterized_joinrel_size_hook =
119+
prev_get_parameterized_joinrel_size_hook;
120+
copy_generic_path_info_hook = prev_copy_generic_path_info_hook;
121+
fini_deactivated_queries_storage();
122+
}
123+
124+
PG_FUNCTION_INFO_V1(invalidate_deactivated_queries_cache);
125+
126+
/*
127+
* Clears the cache of deactivated queries if the user changed aqo_queries
128+
* manually.
129+
*/
130+
Datum
131+
invalidate_deactivated_queries_cache(PG_FUNCTION_ARGS)
132+
{
133+
fini_deactivated_queries_storage();
134+
init_deactivated_queries_storage();
135+
PG_RETURN_POINTER(NULL);
136+
}

‎contrib/aqo/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)