Skip to content

Commit f5cdd04

Browse files
author
Liudmila Mantrova
committed
Included comments from Oleg Ivanov to aqo documentation, added reference section
1 parent a29bab7 commit f5cdd04

File tree

1 file changed

+85
-39
lines changed

1 file changed

+85
-39
lines changed

‎doc/src/sgml/aqo.sgml

Lines changed: 85 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -9,14 +9,8 @@
99

1010
<para>
1111
The <filename>aqo</filename> module is a <productname>&productname;</productname> extension for cost-based query
12-
optimization. Using machine learning methods, in particular, the
13-
k-NN algorithm, <filename>aqo</filename> improves cardinality estimation, which can speed
14-
up query execution. In the learning modes, <filename>aqo</filename> collects statistics on all the executed
15-
queries and classifies it according to the query type. If the
16-
queries differ in their constants only, they belong to the same
17-
type. For each type, <filename>aqo</filename> stores the cardinality quality, planning
18-
time, and execution time. Based on this data, <filename>aqo</filename> builds the new query
19-
plan and uses it for the next query of the same type.
12+
optimization. Using machine learning methods, more precisely, a modification of the
13+
k-NN algorithm, <filename>aqo</filename> improves cardinality estimation, which can improve execution plans and, consequently, speed up query execution.
2014
</para>
2115
<para>
2216
With <filename>aqo</filename>, you can:
@@ -29,11 +23,15 @@
2923
</listitem>
3024
<listitem>
3125
<para>
32-
Experiment with plans for complex query types
26+
Experiment with execution plans for complex queries
3327
</para>
3428
</listitem>
3529
</itemizedlist>
36-
<para>
30+
<para>In the learning modes, <filename>aqo</filename> collects statistics on all the executed
31+
queries and classifies it according to the query type. If the
32+
queries differ in their constants only, they belong to the same
33+
type. For each type, <filename>aqo</filename> stores the cardinality quality, planning
34+
time, execution time, and execution statistics for machine learning. Based on this data, <filename>aqo</filename> builds the new query plan and uses it for the next query of the same type.
3735
Experimental evaluation shows that <filename>aqo</filename> can significantly improve
3836
performance for complex queries.
3937
</para>
@@ -67,7 +65,7 @@ shared_preload_libraries = 'aqo.so'
6765
From the command line, run the following query:
6866
</para>
6967
<programlisting>
70-
$ psql -d <replaceable>dbname</replaceable> -c &quot;CREATE EXTENSION aqo&quot;
68+
$ psql -d <replaceable>dbname</replaceable> -c &quot;CREATE EXTENSION aqo;&quot;
7169
</programlisting>
7270
</listitem>
7371
</orderedlist>
@@ -80,7 +78,7 @@ $ psql -d <replaceable>dbname</replaceable> -c &quot;CREATE EXTENSION aqo&quot;
8078
To stop using <filename>aqo</filename> for query optimization, run:
8179
</para>
8280
<programlisting>
83-
DROP EXTENSION aqo
81+
DROP EXTENSION aqo;
8482
</programlisting>
8583
</sect2>
8684
<sect2 id="aqo-usage">
@@ -125,28 +123,33 @@ DROP EXTENSION aqo
125123
To dynamically change the <filename>aqo</filename> settings in your current session,
126124
run the following command:
127125
<programlisting>
128-
set aqo.mode = <replaceable>'mode'</>
126+
SET aqo.mode = <replaceable>'mode'</>;
129127
</programlisting>
130128
where <replaceable>mode</> is the name of the optimization mode to use.
131129
</para>
132130
<blockquote>
133131
<para>
134132
<emphasis role="strong">Important:</emphasis> The <filename>aqo</filename>
135-
extension may not work well for queries with dynamically
133+
extension will not work well for queries with dynamically
136134
generated views. If such queries appear in your workload,
137135
reset the optimization mode to <literal>aqo.mode='manual'</literal>.
138136
</para>
139137
</blockquote>
140138
<para>
141-
If you often run queries of the same type, or a specific query
142-
type takes too long, you can use the intelligent mode to
143-
optimize planning for such queries. In the intelligent mode, <filename>aqo</filename>
144-
analyzes previous query execution and stores statistics in the
145-
<literal>aqo_queries</literal> table. Statistics on queries of
139+
If you often run queries of the same type and some query types takes too long because of non-optimal query execution plans, you can use the intelligent mode to
140+
improve planning for such queries. In the intelligent mode, <filename>aqo</filename>
141+
analyzes previous query execution and stores statistics. Statistics on queries of
146142
different types is stored separately. If performance is not
147143
improved after 50 iterations, the <filename>aqo</filename> extension falls back to
148144
the default query planner.
149145
</para>
146+
<blockquote>
147+
<para>
148+
<emphasis role="strong">Note:</emphasis> You can view the
149+
current query plan using the standard PostgreSQL <command>EXPLAIN</> command with the
150+
<command>ANALYZE</> option. For details, see the <link linkend="using-explain">Using EXPLAIN</link> section.
151+
</para>
152+
</blockquote>
150153
<para>
151154
Since the intelligent mode tries to learn separately for
152155
different query types, <filename>aqo</filename> may fail to optimize queries with
@@ -164,28 +167,17 @@ where <replaceable>mode</> is the name of the optimization mode to use.
164167
manual mode.
165168
</para>
166169
<para>
167-
When you have reached the required optimization level, you can
168-
stop performance tuning and use the new plan for a
169-
particular query type. To disable further performance tuning,
170-
switch <filename>aqo</filename> to the manual mode. This can help you avoid tuning
171-
overhead if you run multiple simple queries of the same type:
170+
When you have reached the required optimization level in the intelligent mode, you can stop performance tuning by switching <filename>aqo</filename> to the manual mode:
172171
</para>
173172
<programlisting>
174-
set aqo.mode = manual
173+
SET aqo.mode = 'manual';
175174
</programlisting>
176175
<para>
177176
In the manual mode, <filename>aqo</filename> does not collect statistics for new
178177
query types, so they will not be optimized. For known query
179178
types, <filename>aqo</filename> will continue using the optimized planning
180179
algorithms.
181180
</para>
182-
<blockquote>
183-
<para>
184-
<emphasis role="strong">Note:</emphasis> You can view the
185-
current query plan using the standard PostgreSQL <command>EXPLAIN</> command with the
186-
<command>ANALYZE</> option. For details, see the <link linkend="using-explain">Using EXPLAIN</link> section.
187-
</para>
188-
</blockquote>
189181
</sect3>
190182
<sect3 id="aqo-advanced-query-tuning">
191183
<title>Advanced Query Tuning</title>
@@ -204,15 +196,15 @@ set aqo.mode = manual
204196
the <literal>aqo_query_texts</literal> table:
205197
</para>
206198
<programlisting>
207-
select * from aqo_query_texts;
199+
SELECT * FROM aqo_query_texts;
208200
</programlisting>
209201
<para>
210202
Each query type has its own optimization settings. To review the
211203
current settings for each type, view the
212204
<literal>aqo_queries</literal> table:
213205
</para>
214206
<programlisting>
215-
select * from aqo_queries;
207+
SELECT * FROM aqo_queries;
216208
</programlisting>
217209
<para>
218210
For each query type, the following settings are available:
@@ -258,17 +250,32 @@ select * from aqo_queries;
258250
for a particular query type. For example:
259251
</para>
260252
<programlisting>
253+
-- Add a new query type into the aqo_queries table:
254+
261255
SET aqo.mode='intelligent';
262256
SELECT * FROM a, b WHERE a.id=b.id;
263257
SET aqo.mode='manual';
258+
259+
-- Disable auto_tuning, enable both learn_aqo and use_aqo for this query type:
260+
264261
UPDATE aqo_queries SET use_aqo=true, learn_aqo=true, auto_tuning=false
265262
WHERE query_hash = (SELECT query_hash from aqo_query_texts
266263
WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');
264+
265+
-- Run EXPLAIN ANALYZE until the plan changes:
266+
267+
EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;
268+
EXPLAIN ANALYZE SELECT * FROM a, b WHERE a.id=b.id;
269+
270+
-- Disable learning to stop statistics collection and use the optimized plan:
271+
272+
UPDATE aqo_queries SET learn_aqo=false
273+
WHERE query_hash = (SELECT query_hash from aqo_query_texts
274+
WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;');
267275
</programlisting>
268276

269277
<para>
270-
If your data or query distributions are rapidly changing, <filename>aqo</filename> may
271-
learn longer than expected. To speed up learning, reset the
278+
If your data or query distribution is rapidly changing, obsolete statistics may affect AQO performance. After a while, <filename>aqo</filename> will learn the new statistics, but it may take longer than expected. To speed up learning, reset the
272279
statistics. To remove all the collected machine learning
273280
statistics, run the following command:
274281
</para>
@@ -285,26 +292,65 @@ DELETE FROM aqo_data WHERE fspace_hash = (SELECT fspace_hash FROM aqo_queries
285292
WHERE query_text LIKE 'SELECT * FROM a, b WHERE a.id=b.id;'));
286293
</programlisting>
287294

295+
<para>
296+
To stop intelligent tuning for a particular query type, disable the <literal>auto_tuning</literal> setting:
297+
<programlisting>
298+
UPDATE aqo_queries SET auto_tuning=false WHERE query_hash = '<replaceable>hash</>';
299+
</programlisting>
300+
where <replaceable>hash</> is the hash value for this query type. As a result, <filename>aqo</filename> disables automatic changing of the <literal>learn_aqo</literal> and <literal>use_aqo</literal> settings.
301+
</para>
302+
288303
<para>
289304
To disable further learning for a particular query type, use the
290305
following command:
291306
<programlisting>
292-
UPDATE aqo_queries SET auto_tuning=false WHERE fspase_hash = '<replaceable>hash</>';
307+
UPDATE aqo_queries SET learn_aqo=false WHERE query_hash = '<replaceable>hash</>';
293308
</programlisting>
294309
where <replaceable>hash</> is the hash value for this query type.
295310
</para>
296311

297312
<para>
298-
To disable <filename>aqo</filename> for all queries and use the default <productname>PostgreSQL</>
313+
To fully disable <filename>aqo</filename> for all queries and use the default <productname>PostgreSQL</>
299314
query planner, run:
300315
</para>
301316
<programlisting>
302-
UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;
317+
UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;s
303318
</programlisting>
304319
</sect3>
305320
</sect2>
306321
<sect2 id="aqo-reference">
307322
<title>Reference</title>
323+
<sect3 id="aqo-variables">
324+
<title>Configuration Variables</title>
325+
<para>The aqo extension provides the following configuration variables:
326+
</para>
327+
<sect4 id="aqo-mode">
328+
<title>aqo.mode</title>
329+
<para>Defines the <filename>aqo</filename> optimization modes.
330+
</para>
331+
<para><emphasis role="strong">Options:</emphasis>
332+
</para>
333+
<itemizedlist spacing="compact">
334+
<listitem>
335+
<para>
336+
<literal>intelligent</literal> - auto-tunes your queries based on statistics collected per query type.
337+
</para>
338+
</listitem>
339+
<listitem>
340+
<para>
341+
<literal>forced</literal> - optimizes all queries together, regardless of the query type.
342+
</para>
343+
</listitem>
344+
<listitem>
345+
<para>
346+
<literal>manual</literal> (default) - uses the default planner for all new queries, but can reuse the collected statistics for already known query types, if any.
347+
</para>
348+
</listitem>
349+
</itemizedlist>
350+
</sect4>
351+
352+
353+
</sect3>
308354
<sect3 id="aqo-tables">
309355
<title>Tables</title>
310356
<sect4 id="aqo-query-texts-table">

0 commit comments

Comments
 (0)