Skip to content

Commit 7a62f23

Browse files
author
Liudmila Mantrova
committed
Documentation for PGPRO-362 - disabled mode, bug fixes based on A.Lakhin feedback
1 parent 7f7c2d1 commit 7a62f23

File tree

1 file changed

+31
-18
lines changed

1 file changed

+31
-18
lines changed

‎doc/src/sgml/aqo.sgml

Lines changed: 31 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
</para>
2323
</listitem>
2424
<listitem>
25-
<para>
25+
<para>
2626
Experiment with execution plans for complex queries
2727
</para>
2828
</listitem>
@@ -56,7 +56,7 @@ shared_preload_libraries = 'aqo'
5656
</programlisting>
5757
<para>
5858
You should preload the `aqo` library at the server startup, since
59-
adaptive query optimization needs to be enabled per database, not per connection. Otherwise, all the collected statistics will be lost when the session ends.
59+
adaptive query optimization needs to be enabled per cluster. Otherwise, the <filename>aqo</filename> will only be used for the session in which you created the <filename>aqo</filename> extension.
6060
</para>
6161
</listitem>
6262
<listitem>
@@ -74,7 +74,7 @@ $ psql -d <replaceable>dbname</replaceable> -c &quot;CREATE EXTENSION aqo;&quot;
7474
you can start optimizing queries.
7575
</para>
7676
<para>
77-
To stop using <filename>aqo</filename> for query optimization, run:
77+
To permanently disable <filename>aqo</filename>, run:
7878
</para>
7979
<programlisting>
8080
DROP EXTENSION aqo;
@@ -98,31 +98,39 @@ DROP EXTENSION aqo;
9898
<itemizedlist spacing="compact">
9999
<listitem>
100100
<para>
101-
<literal>aqo.mode = 'intelligent'</literal> - this
101+
<varname>aqo.mode</varname> = <literal>'intelligent'</literal> &mdash; this
102102
mode auto-tunes your queries based on statistics collected per
103103
query type.
104104
</para>
105105
</listitem>
106106
<listitem>
107-
<para>
108-
<literal>aqo.mode = 'forced'</literal> -
107+
<para>
108+
<varname>aqo.mode</varname> = <literal>'forced'</literal> &mdash
109109
this mode tries to optimize all queries together, regardless of
110110
the query type.
111111
</para>
112-
</listitem>
112+
</listitem>
113113
<listitem>
114-
<para>
115-
<literal>aqo.mode = 'manual'</literal> - this mode
114+
<para>
115+
<varname>aqo.mode</varname> = <literal>'manual'</literal> &mdash; this mode
116116
uses the default planner for all new queries, but can reuse the
117117
collected statistics for already known query types, if any.
118118
</para>
119119
</listitem>
120+
<listitem>
121+
<para>
122+
<varname>aqo.mode</varname> = <literal>'disabled'</literal> &mdash; this mode
123+
disables <filename>aqo</filename> for all queries, even for the known query types.
124+
You can use this mode to temporarily disable <filename>aqo</filename> without losing
125+
the collected statistics and configuration.
126+
</para>
127+
</listitem>
120128
</itemizedlist>
121129
<para>
122130
To dynamically change the <filename>aqo</filename> settings in your current session,
123131
run the following command:
124132
<programlisting>
125-
SET aqo.mode = <replaceable>'mode'</>;
133+
SET aqo.mode = '<replaceable>mode</>';
126134
</programlisting>
127135
where <replaceable>mode</> is the name of the optimization mode to use.
128136
</para>
@@ -145,15 +153,15 @@ where <replaceable>mode</> is the name of the optimization mode to use.
145153
<blockquote>
146154
<para>
147155
<emphasis role="strong">Note:</emphasis> You can view the
148-
current query plan using the standard PostgreSQL <command>EXPLAIN</> command with the
149-
<command>ANALYZE</> option. For details, see the <link linkend="using-explain">Using EXPLAIN</link> section.
156+
current query plan using the standard <productname>PostgreSQL</productname> <command>EXPLAIN</> command with the
157+
<command>ANALYZE</> option. For details, see the <xref linkend="using-explain">.
150158
</para>
151159
</blockquote>
152160
<para>
153161
Since the intelligent mode tries to learn separately for
154162
different query types, <filename>aqo</filename> may fail to optimize queries with
155163
dynamic structure. For such cases, reset the <filename>aqo</filename> extension to
156-
the manual mode, or try using the forced mode.
164+
the <literal>manual</literal> mode, or try using the forced mode.
157165
</para>
158166
<para>
159167
In the <literal>forced</literal> mode, <filename>aqo</filename> ignores different
@@ -163,7 +171,7 @@ where <replaceable>mode</> is the name of the optimization mode to use.
163171
mode. However, since the <literal>forced</literal> mode lacks
164172
intelligent tuning, performance may decrease for some queries.
165173
If you see performance issues in this mode, switch <filename>aqo</filename> to the
166-
manual mode.
174+
<literal>manual</literal> mode.
167175
</para>
168176
<para>
169177
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:
@@ -172,11 +180,12 @@ where <replaceable>mode</> is the name of the optimization mode to use.
172180
SET aqo.mode = 'manual';
173181
</programlisting>
174182
<para>
175-
In the manual mode, <filename>aqo</filename> does not collect statistics for new
183+
In the <literal>manual</literal> mode, <filename>aqo</filename> does not collect statistics for new
176184
query types, so they will not be optimized. For known query
177185
types, <filename>aqo</filename> will continue using the optimized planning
178186
algorithms.
179187
</para>
188+
<para>If you want to fully disable <filename>aqo</filename>, you can switch <filename>aqo</filename> to the <literal>disabled</literal> mode. In this case, the default planner is used for all queries, but the collected statistics and <filename>aqo</filename> settings are saved and can be used in the future. </para>
180189
</sect3>
181190
<sect3 id="aqo-advanced-query-tuning">
182191
<title>Advanced Query Tuning</title>
@@ -239,7 +248,7 @@ SELECT * FROM aqo_queries;
239248
<para>
240249
<literal>auto_tuning</literal> shows whether
241250
<filename>aqo</filename> tries to tune other settings for the
242-
given query. By default, auto-tuning in enabled in the
251+
given query. By default, auto-tuning is enabled in the
243252
intelligent mode.
244253
</para>
245254
</listitem>
@@ -294,7 +303,7 @@ DELETE FROM aqo_data WHERE fspace_hash = (SELECT fspace_hash FROM aqo_queries
294303
<para>
295304
To stop intelligent tuning for a particular query type, disable the <literal>auto_tuning</literal> setting:
296305
<programlisting>
297-
UPDATE aqo_queries SET auto_tuning=false WHERE query_hash = '<replaceable>hash</>';
306+
UPDATE aqo_queries SET auto_tuning=false WHERE query_hash = '<replaceable>hash</>';
298307
</programlisting>
299308
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.
300309
</para>
@@ -303,7 +312,7 @@ DELETE FROM aqo_data WHERE fspace_hash = (SELECT fspace_hash FROM aqo_queries
303312
To disable further learning for a particular query type, use the
304313
following command:
305314
<programlisting>
306-
UPDATE aqo_queries SET learn_aqo=false WHERE query_hash = '<replaceable>hash</>';
315+
UPDATE aqo_queries SET learn_aqo=false WHERE query_hash = '<replaceable>hash</>';
307316
</programlisting>
308317
where <replaceable>hash</> is the hash value for this query type.
309318
</para>
@@ -348,6 +357,10 @@ UPDATE aqo_queries SET use_aqo=false, learn_aqo=false, auto_tuning=false;
348357
<entry><literal>manual</literal></entry>
349358
<entry><emphasis role="strong">Default.</emphasis> Uses the default planner for all new queries, but can reuse the collected statistics for already known query types, if any.</entry>
350359
</row>
360+
<row>
361+
<entry><literal>disabled</literal></entry>
362+
<entry>Fully disables <filename>aqo</filename> for all queries. The collected statistics and <filename>aqo</filename> settings are saved and can be used in the future. </entry>
363+
</row>
351364
</tbody>
352365
</tgroup>
353366
</table>

0 commit comments

Comments
 (0)