1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>19.7. Query Planning</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-replication.html" title="19.6. Replication" /><link rel="next" href="runtime-config-logging.html" title="19.8. Error Reporting and Logging" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.7. Query Planning</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-replication.html" title="19.6. Replication">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-logging.html" title="19.8. Error Reporting and Logging">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-QUERY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.7. Query Planning <a href="#RUNTIME-CONFIG-QUERY" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE">19.7.1. Planner Method Configuration</a></span></dt><dt><span class="sect2"><a href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS">19.7.2. Planner Cost Constants</a></span></dt><dt><span class="sect2"><a href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO">19.7.3. Genetic Query Optimizer</a></span></dt><dt><span class="sect2"><a href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-OTHER">19.7.4. Other Planner Options</a></span></dt></dl></div><div class="sect2" id="RUNTIME-CONFIG-QUERY-ENABLE"><div class="titlepage"><div><div><h3 class="title">19.7.1. Planner Method Configuration <a href="#RUNTIME-CONFIG-QUERY-ENABLE" class="id_link">#</a></h3></div></div></div><p>
3 These configuration parameters provide a crude method of
4 influencing the query plans chosen by the query optimizer. If
5 the default plan chosen by the optimizer for a particular query
6 is not optimal, a <span class="emphasis"><em>temporary</em></span> solution is to use one
7 of these configuration parameters to force the optimizer to
8 choose a different plan.
9 Better ways to improve the quality of the
10 plans chosen by the optimizer include adjusting the planner cost
11 constants (see <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS" title="19.7.2. Planner Cost Constants">Section 19.7.2</a>),
12 running <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> manually, increasing
13 the value of the <a class="xref" href="runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET">default_statistics_target</a> configuration parameter,
14 and increasing the amount of statistics collected for
15 specific columns using <code class="command">ALTER TABLE SET
17 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-ENABLE-ASYNC-APPEND"><span class="term"><code class="varname">enable_async_append</code> (<code class="type">boolean</code>)
18 <a id="id-1.6.6.10.2.3.1.1.3" class="indexterm"></a>
19 </span> <a href="#GUC-ENABLE-ASYNC-APPEND" class="id_link">#</a></dt><dd><p>
20 Enables or disables the query planner's use of async-aware
21 append plan types. The default is <code class="literal">on</code>.
22 </p></dd><dt id="GUC-ENABLE-BITMAPSCAN"><span class="term"><code class="varname">enable_bitmapscan</code> (<code class="type">boolean</code>)
23 <a id="id-1.6.6.10.2.3.2.1.3" class="indexterm"></a>
24 <a id="id-1.6.6.10.2.3.2.1.4" class="indexterm"></a>
25 </span> <a href="#GUC-ENABLE-BITMAPSCAN" class="id_link">#</a></dt><dd><p>
26 Enables or disables the query planner's use of bitmap-scan plan
27 types. The default is <code class="literal">on</code>.
28 </p></dd><dt id="GUC-ENABLE-DISTINCT-REORDERING"><span class="term"><code class="varname">enable_distinct_reordering</code> (<code class="type">boolean</code>)
29 <a id="id-1.6.6.10.2.3.3.1.3" class="indexterm"></a>
30 </span> <a href="#GUC-ENABLE-DISTINCT-REORDERING" class="id_link">#</a></dt><dd><p>
31 Enables or disables the query planner's ability to reorder DISTINCT
32 keys to match the input path's pathkeys. The default is <code class="literal">on</code>.
33 </p></dd><dt id="GUC-ENABLE-GATHERMERGE"><span class="term"><code class="varname">enable_gathermerge</code> (<code class="type">boolean</code>)
34 <a id="id-1.6.6.10.2.3.4.1.3" class="indexterm"></a>
35 </span> <a href="#GUC-ENABLE-GATHERMERGE" class="id_link">#</a></dt><dd><p>
36 Enables or disables the query planner's use of gather
37 merge plan types. The default is <code class="literal">on</code>.
38 </p></dd><dt id="GUC-ENABLE-GROUPBY-REORDERING"><span class="term"><code class="varname">enable_group_by_reordering</code> (<code class="type">boolean</code>)
39 <a id="id-1.6.6.10.2.3.5.1.3" class="indexterm"></a>
40 </span> <a href="#GUC-ENABLE-GROUPBY-REORDERING" class="id_link">#</a></dt><dd><p>
41 Controls if the query planner will produce a plan which will provide
42 <code class="literal">GROUP BY</code> keys sorted in the order of keys of
43 a child node of the plan, such as an index scan. When disabled, the
44 query planner will produce a plan with <code class="literal">GROUP BY</code>
45 keys only sorted to match the <code class="literal">ORDER BY</code> clause,
46 if any. When enabled, the planner will try to produce a more
47 efficient plan. The default value is <code class="literal">on</code>.
48 </p></dd><dt id="GUC-ENABLE-HASHAGG"><span class="term"><code class="varname">enable_hashagg</code> (<code class="type">boolean</code>)
49 <a id="id-1.6.6.10.2.3.6.1.3" class="indexterm"></a>
50 </span> <a href="#GUC-ENABLE-HASHAGG" class="id_link">#</a></dt><dd><p>
51 Enables or disables the query planner's use of hashed
52 aggregation plan types. The default is <code class="literal">on</code>.
53 </p></dd><dt id="GUC-ENABLE-HASHJOIN"><span class="term"><code class="varname">enable_hashjoin</code> (<code class="type">boolean</code>)
54 <a id="id-1.6.6.10.2.3.7.1.3" class="indexterm"></a>
55 </span> <a href="#GUC-ENABLE-HASHJOIN" class="id_link">#</a></dt><dd><p>
56 Enables or disables the query planner's use of hash-join plan
57 types. The default is <code class="literal">on</code>.
58 </p></dd><dt id="GUC-ENABLE-INCREMENTAL-SORT"><span class="term"><code class="varname">enable_incremental_sort</code> (<code class="type">boolean</code>)
59 <a id="id-1.6.6.10.2.3.8.1.3" class="indexterm"></a>
60 </span> <a href="#GUC-ENABLE-INCREMENTAL-SORT" class="id_link">#</a></dt><dd><p>
61 Enables or disables the query planner's use of incremental sort steps.
62 The default is <code class="literal">on</code>.
63 </p></dd><dt id="GUC-ENABLE-INDEXSCAN"><span class="term"><code class="varname">enable_indexscan</code> (<code class="type">boolean</code>)
64 <a id="id-1.6.6.10.2.3.9.1.3" class="indexterm"></a>
65 <a id="id-1.6.6.10.2.3.9.1.4" class="indexterm"></a>
66 </span> <a href="#GUC-ENABLE-INDEXSCAN" class="id_link">#</a></dt><dd><p>
67 Enables or disables the query planner's use of index-scan and
68 index-only-scan plan types. The default is <code class="literal">on</code>.
69 Also see <a class="xref" href="runtime-config-query.html#GUC-ENABLE-INDEXONLYSCAN">enable_indexonlyscan</a>.
70 </p></dd><dt id="GUC-ENABLE-INDEXONLYSCAN"><span class="term"><code class="varname">enable_indexonlyscan</code> (<code class="type">boolean</code>)
71 <a id="id-1.6.6.10.2.3.10.1.3" class="indexterm"></a>
72 </span> <a href="#GUC-ENABLE-INDEXONLYSCAN" class="id_link">#</a></dt><dd><p>
73 Enables or disables the query planner's use of index-only-scan plan
74 types (see <a class="xref" href="indexes-index-only-scans.html" title="11.9. Index-Only Scans and Covering Indexes">Section 11.9</a>).
75 The default is <code class="literal">on</code>. The
76 <a class="xref" href="runtime-config-query.html#GUC-ENABLE-INDEXSCAN">enable_indexscan</a> setting must also be
77 enabled to have the query planner consider index-only-scans.
78 </p></dd><dt id="GUC-ENABLE-MATERIAL"><span class="term"><code class="varname">enable_material</code> (<code class="type">boolean</code>)
79 <a id="id-1.6.6.10.2.3.11.1.3" class="indexterm"></a>
80 </span> <a href="#GUC-ENABLE-MATERIAL" class="id_link">#</a></dt><dd><p>
81 Enables or disables the query planner's use of materialization.
82 It is impossible to suppress materialization entirely,
83 but turning this variable off prevents the planner from inserting
84 materialize nodes except in cases where it is required for correctness.
85 The default is <code class="literal">on</code>.
86 </p></dd><dt id="GUC-ENABLE-MEMOIZE"><span class="term"><code class="varname">enable_memoize</code> (<code class="type">boolean</code>)
87 <a id="id-1.6.6.10.2.3.12.1.3" class="indexterm"></a>
88 </span> <a href="#GUC-ENABLE-MEMOIZE" class="id_link">#</a></dt><dd><p>
89 Enables or disables the query planner's use of memoize plans for
90 caching results from parameterized scans inside nested-loop joins.
91 This plan type allows scans to the underlying plans to be skipped when
92 the results for the current parameters are already in the cache. Less
93 commonly looked up results may be evicted from the cache when more
94 space is required for new entries. The default is
95 <code class="literal">on</code>.
96 </p></dd><dt id="GUC-ENABLE-MERGEJOIN"><span class="term"><code class="varname">enable_mergejoin</code> (<code class="type">boolean</code>)
97 <a id="id-1.6.6.10.2.3.13.1.3" class="indexterm"></a>
98 </span> <a href="#GUC-ENABLE-MERGEJOIN" class="id_link">#</a></dt><dd><p>
99 Enables or disables the query planner's use of merge-join plan
100 types. The default is <code class="literal">on</code>.
101 </p></dd><dt id="GUC-ENABLE-NESTLOOP"><span class="term"><code class="varname">enable_nestloop</code> (<code class="type">boolean</code>)
102 <a id="id-1.6.6.10.2.3.14.1.3" class="indexterm"></a>
103 </span> <a href="#GUC-ENABLE-NESTLOOP" class="id_link">#</a></dt><dd><p>
104 Enables or disables the query planner's use of nested-loop join
105 plans. It is impossible to suppress nested-loop joins entirely,
106 but turning this variable off discourages the planner from using
107 one if there are other methods available. The default is
108 <code class="literal">on</code>.
109 </p></dd><dt id="GUC-ENABLE-PARALLEL-APPEND"><span class="term"><code class="varname">enable_parallel_append</code> (<code class="type">boolean</code>)
110 <a id="id-1.6.6.10.2.3.15.1.3" class="indexterm"></a>
111 </span> <a href="#GUC-ENABLE-PARALLEL-APPEND" class="id_link">#</a></dt><dd><p>
112 Enables or disables the query planner's use of parallel-aware
113 append plan types. The default is <code class="literal">on</code>.
114 </p></dd><dt id="GUC-ENABLE-PARALLEL-HASH"><span class="term"><code class="varname">enable_parallel_hash</code> (<code class="type">boolean</code>)
115 <a id="id-1.6.6.10.2.3.16.1.3" class="indexterm"></a>
116 </span> <a href="#GUC-ENABLE-PARALLEL-HASH" class="id_link">#</a></dt><dd><p>
117 Enables or disables the query planner's use of hash-join plan
118 types with parallel hash. Has no effect if hash-join plans are not
119 also enabled. The default is <code class="literal">on</code>.
120 </p></dd><dt id="GUC-ENABLE-PARTITION-PRUNING"><span class="term"><code class="varname">enable_partition_pruning</code> (<code class="type">boolean</code>)
121 <a id="id-1.6.6.10.2.3.17.1.3" class="indexterm"></a>
122 </span> <a href="#GUC-ENABLE-PARTITION-PRUNING" class="id_link">#</a></dt><dd><p>
123 Enables or disables the query planner's ability to eliminate a
124 partitioned table's partitions from query plans. This also controls
125 the planner's ability to generate query plans which allow the query
126 executor to remove (ignore) partitions during query execution. The
127 default is <code class="literal">on</code>.
128 See <a class="xref" href="ddl-partitioning.html#DDL-PARTITION-PRUNING" title="5.12.4. Partition Pruning">Section 5.12.4</a> for details.
129 </p></dd><dt id="GUC-ENABLE-PARTITIONWISE-JOIN"><span class="term"><code class="varname">enable_partitionwise_join</code> (<code class="type">boolean</code>)
130 <a id="id-1.6.6.10.2.3.18.1.3" class="indexterm"></a>
131 </span> <a href="#GUC-ENABLE-PARTITIONWISE-JOIN" class="id_link">#</a></dt><dd><p>
132 Enables or disables the query planner's use of partitionwise join,
133 which allows a join between partitioned tables to be performed by
134 joining the matching partitions. Partitionwise join currently applies
135 only when the join conditions include all the partition keys, which
136 must be of the same data type and have one-to-one matching sets of
137 child partitions. With this setting enabled, the number of nodes
138 whose memory usage is restricted by <code class="varname">work_mem</code>
139 appearing in the final plan can increase linearly according to the
140 number of partitions being scanned. This can result in a large
141 increase in overall memory consumption during the execution of the
142 query. Query planning also becomes significantly more expensive in
143 terms of memory and CPU. The default value is <code class="literal">off</code>.
144 </p></dd><dt id="GUC-ENABLE-PARTITIONWISE-AGGREGATE"><span class="term"><code class="varname">enable_partitionwise_aggregate</code> (<code class="type">boolean</code>)
145 <a id="id-1.6.6.10.2.3.19.1.3" class="indexterm"></a>
146 </span> <a href="#GUC-ENABLE-PARTITIONWISE-AGGREGATE" class="id_link">#</a></dt><dd><p>
147 Enables or disables the query planner's use of partitionwise grouping
148 or aggregation, which allows grouping or aggregation on partitioned
149 tables to be performed separately for each partition. If the
150 <code class="literal">GROUP BY</code> clause does not include the partition
151 keys, only partial aggregation can be performed on a per-partition
152 basis, and finalization must be performed later. With this setting
153 enabled, the number of nodes whose memory usage is restricted by
154 <code class="varname">work_mem</code> appearing in the final plan can increase
155 linearly according to the number of partitions being scanned. This
156 can result in a large increase in overall memory consumption during
157 the execution of the query. Query planning also becomes significantly
158 more expensive in terms of memory and CPU. The default value is
159 <code class="literal">off</code>.
160 </p></dd><dt id="GUC-ENABLE-PRESORTED-AGGREGATE"><span class="term"><code class="varname">enable_presorted_aggregate</code> (<code class="type">boolean</code>)
161 <a id="id-1.6.6.10.2.3.20.1.3" class="indexterm"></a>
162 </span> <a href="#GUC-ENABLE-PRESORTED-AGGREGATE" class="id_link">#</a></dt><dd><p>
163 Controls if the query planner will produce a plan which will provide
164 rows which are presorted in the order required for the query's
165 <code class="literal">ORDER BY</code> / <code class="literal">DISTINCT</code> aggregate
166 functions. When disabled, the query planner will produce a plan which
167 will always require the executor to perform a sort before performing
168 aggregation of each aggregate function containing an
169 <code class="literal">ORDER BY</code> or <code class="literal">DISTINCT</code> clause.
170 When enabled, the planner will try to produce a more efficient plan
171 which provides input to the aggregate functions which is presorted in
172 the order they require for aggregation. The default value is
173 <code class="literal">on</code>.
174 </p></dd><dt id="GUC-ENABLE-SELF-JOIN-ELIMINATION"><span class="term"><code class="varname">enable_self_join_elimination</code> (<code class="type">boolean</code>)
175 <a id="id-1.6.6.10.2.3.21.1.3" class="indexterm"></a>
176 </span> <a href="#GUC-ENABLE-SELF-JOIN-ELIMINATION" class="id_link">#</a></dt><dd><p>
177 Enables or disables the query planner's optimization which analyses
178 the query tree and replaces self joins with semantically equivalent
179 single scans. Takes into consideration only plain tables.
180 The default is <code class="literal">on</code>.
181 </p></dd><dt id="GUC-ENABLE-SEQSCAN"><span class="term"><code class="varname">enable_seqscan</code> (<code class="type">boolean</code>)
182 <a id="id-1.6.6.10.2.3.22.1.3" class="indexterm"></a>
183 <a id="id-1.6.6.10.2.3.22.1.4" class="indexterm"></a>
184 </span> <a href="#GUC-ENABLE-SEQSCAN" class="id_link">#</a></dt><dd><p>
185 Enables or disables the query planner's use of sequential scan
186 plan types. It is impossible to suppress sequential scans
187 entirely, but turning this variable off discourages the planner
188 from using one if there are other methods available. The
189 default is <code class="literal">on</code>.
190 </p></dd><dt id="GUC-ENABLE-SORT"><span class="term"><code class="varname">enable_sort</code> (<code class="type">boolean</code>)
191 <a id="id-1.6.6.10.2.3.23.1.3" class="indexterm"></a>
192 </span> <a href="#GUC-ENABLE-SORT" class="id_link">#</a></dt><dd><p>
193 Enables or disables the query planner's use of explicit sort
194 steps. It is impossible to suppress explicit sorts entirely,
195 but turning this variable off discourages the planner from
196 using one if there are other methods available. The default
197 is <code class="literal">on</code>.
198 </p></dd><dt id="GUC-ENABLE-TIDSCAN"><span class="term"><code class="varname">enable_tidscan</code> (<code class="type">boolean</code>)
199 <a id="id-1.6.6.10.2.3.24.1.3" class="indexterm"></a>
200 </span> <a href="#GUC-ENABLE-TIDSCAN" class="id_link">#</a></dt><dd><p>
201 Enables or disables the query planner's use of <acronym class="acronym">TID</acronym>
202 scan plan types. The default is <code class="literal">on</code>.
203 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-QUERY-CONSTANTS"><div class="titlepage"><div><div><h3 class="title">19.7.2. Planner Cost Constants <a href="#RUNTIME-CONFIG-QUERY-CONSTANTS" class="id_link">#</a></h3></div></div></div><p>
204 The <em class="firstterm">cost</em> variables described in this section are measured
205 on an arbitrary scale. Only their relative values matter, hence
206 scaling them all up or down by the same factor will result in no change
207 in the planner's choices. By default, these cost variables are based on
208 the cost of sequential page fetches; that is,
209 <code class="varname">seq_page_cost</code> is conventionally set to <code class="literal">1.0</code>
210 and the other cost variables are set with reference to that. But
211 you can use a different scale if you prefer, such as actual execution
212 times in milliseconds on a particular machine.
213 </p><div class="note"><h3 class="title">Note</h3><p>
214 Unfortunately, there is no well-defined method for determining ideal
215 values for the cost variables. They are best treated as averages over
216 the entire mix of queries that a particular installation will receive. This
217 means that changing them on the basis of just a few experiments is very
219 </p></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-SEQ-PAGE-COST"><span class="term"><code class="varname">seq_page_cost</code> (<code class="type">floating point</code>)
220 <a id="id-1.6.6.10.3.4.1.1.3" class="indexterm"></a>
221 </span> <a href="#GUC-SEQ-PAGE-COST" class="id_link">#</a></dt><dd><p>
222 Sets the planner's estimate of the cost of a disk page fetch
223 that is part of a series of sequential fetches. The default is 1.0.
224 This value can be overridden for tables and indexes in a particular
225 tablespace by setting the tablespace parameter of the same name
226 (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).
227 </p></dd><dt id="GUC-RANDOM-PAGE-COST"><span class="term"><code class="varname">random_page_cost</code> (<code class="type">floating point</code>)
228 <a id="id-1.6.6.10.3.4.2.1.3" class="indexterm"></a>
229 </span> <a href="#GUC-RANDOM-PAGE-COST" class="id_link">#</a></dt><dd><p>
230 Sets the planner's estimate of the cost of a
231 non-sequentially-fetched disk page. The default is 4.0.
232 This value can be overridden for tables and indexes in a particular
233 tablespace by setting the tablespace parameter of the same name
234 (see <a class="xref" href="sql-altertablespace.html" title="ALTER TABLESPACE"><span class="refentrytitle">ALTER TABLESPACE</span></a>).
236 Reducing this value relative to <code class="varname">seq_page_cost</code>
237 will cause the system to prefer index scans; raising it will
238 make index scans look relatively more expensive. You can raise
239 or lower both values together to change the importance of disk I/O
240 costs relative to CPU costs, which are described by the following
243 Random access to mechanical disk storage is normally much more expensive
244 than four times sequential access. However, a lower default is used
245 (4.0) because the majority of random accesses to disk, such as indexed
246 reads, are assumed to be in cache. The default value can be thought of
247 as modeling random access as 40 times slower than sequential, while
248 expecting 90% of random reads to be cached.
250 If you believe a 90% cache rate is an incorrect assumption
251 for your workload, you can increase random_page_cost to better
252 reflect the true cost of random storage reads. Correspondingly,
253 if your data is likely to be completely in cache, such as when
254 the database is smaller than the total server memory, decreasing
255 random_page_cost can be appropriate. Storage that has a low random
256 read cost relative to sequential, e.g., solid-state drives, might
257 also be better modeled with a lower value for random_page_cost,
258 e.g., <code class="literal">1.1</code>.
259 </p><div class="tip"><h3 class="title">Tip</h3><p>
260 Although the system will let you set <code class="varname">random_page_cost</code> to
261 less than <code class="varname">seq_page_cost</code>, it is not physically sensible
262 to do so. However, setting them equal makes sense if the database
263 is entirely cached in RAM, since in that case there is no penalty
264 for touching pages out of sequence. Also, in a heavily-cached
265 database you should lower both values relative to the CPU parameters,
266 since the cost of fetching a page already in RAM is much smaller
267 than it would normally be.
268 </p></div></dd><dt id="GUC-CPU-TUPLE-COST"><span class="term"><code class="varname">cpu_tuple_cost</code> (<code class="type">floating point</code>)
269 <a id="id-1.6.6.10.3.4.3.1.3" class="indexterm"></a>
270 </span> <a href="#GUC-CPU-TUPLE-COST" class="id_link">#</a></dt><dd><p>
271 Sets the planner's estimate of the cost of processing
272 each row during a query.
274 </p></dd><dt id="GUC-CPU-INDEX-TUPLE-COST"><span class="term"><code class="varname">cpu_index_tuple_cost</code> (<code class="type">floating point</code>)
275 <a id="id-1.6.6.10.3.4.4.1.3" class="indexterm"></a>
276 </span> <a href="#GUC-CPU-INDEX-TUPLE-COST" class="id_link">#</a></dt><dd><p>
277 Sets the planner's estimate of the cost of processing
278 each index entry during an index scan.
279 The default is 0.005.
280 </p></dd><dt id="GUC-CPU-OPERATOR-COST"><span class="term"><code class="varname">cpu_operator_cost</code> (<code class="type">floating point</code>)
281 <a id="id-1.6.6.10.3.4.5.1.3" class="indexterm"></a>
282 </span> <a href="#GUC-CPU-OPERATOR-COST" class="id_link">#</a></dt><dd><p>
283 Sets the planner's estimate of the cost of processing each
284 operator or function executed during a query.
285 The default is 0.0025.
286 </p></dd><dt id="GUC-PARALLEL-SETUP-COST"><span class="term"><code class="varname">parallel_setup_cost</code> (<code class="type">floating point</code>)
287 <a id="id-1.6.6.10.3.4.6.1.3" class="indexterm"></a>
288 </span> <a href="#GUC-PARALLEL-SETUP-COST" class="id_link">#</a></dt><dd><p>
289 Sets the planner's estimate of the cost of launching parallel worker
292 </p></dd><dt id="GUC-PARALLEL-TUPLE-COST"><span class="term"><code class="varname">parallel_tuple_cost</code> (<code class="type">floating point</code>)
293 <a id="id-1.6.6.10.3.4.7.1.3" class="indexterm"></a>
294 </span> <a href="#GUC-PARALLEL-TUPLE-COST" class="id_link">#</a></dt><dd><p>
295 Sets the planner's estimate of the cost of transferring one tuple
296 from a parallel worker process to another process.
298 </p></dd><dt id="GUC-MIN-PARALLEL-TABLE-SCAN-SIZE"><span class="term"><code class="varname">min_parallel_table_scan_size</code> (<code class="type">integer</code>)
299 <a id="id-1.6.6.10.3.4.8.1.3" class="indexterm"></a>
300 </span> <a href="#GUC-MIN-PARALLEL-TABLE-SCAN-SIZE" class="id_link">#</a></dt><dd><p>
301 Sets the minimum amount of table data that must be scanned in order
302 for a parallel scan to be considered. For a parallel sequential scan,
303 the amount of table data scanned is always equal to the size of the
304 table, but when indexes are used the amount of table data
305 scanned will normally be less.
306 If this value is specified without units, it is taken as blocks,
307 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
308 The default is 8 megabytes (<code class="literal">8MB</code>).
309 </p></dd><dt id="GUC-MIN-PARALLEL-INDEX-SCAN-SIZE"><span class="term"><code class="varname">min_parallel_index_scan_size</code> (<code class="type">integer</code>)
310 <a id="id-1.6.6.10.3.4.9.1.3" class="indexterm"></a>
311 </span> <a href="#GUC-MIN-PARALLEL-INDEX-SCAN-SIZE" class="id_link">#</a></dt><dd><p>
312 Sets the minimum amount of index data that must be scanned in order
313 for a parallel scan to be considered. Note that a parallel index scan
314 typically won't touch the entire index; it is the number of pages
315 which the planner believes will actually be touched by the scan which
316 is relevant. This parameter is also used to decide whether a
317 particular index can participate in a parallel vacuum. See
318 <a class="xref" href="sql-vacuum.html" title="VACUUM"><span class="refentrytitle">VACUUM</span></a>.
319 If this value is specified without units, it is taken as blocks,
320 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
321 The default is 512 kilobytes (<code class="literal">512kB</code>).
322 </p></dd><dt id="GUC-EFFECTIVE-CACHE-SIZE"><span class="term"><code class="varname">effective_cache_size</code> (<code class="type">integer</code>)
323 <a id="id-1.6.6.10.3.4.10.1.3" class="indexterm"></a>
324 </span> <a href="#GUC-EFFECTIVE-CACHE-SIZE" class="id_link">#</a></dt><dd><p>
325 Sets the planner's assumption about the effective size of the
326 disk cache that is available to a single query. This is
327 factored into estimates of the cost of using an index; a
328 higher value makes it more likely index scans will be used, a
329 lower value makes it more likely sequential scans will be
330 used. When setting this parameter you should consider both
331 <span class="productname">PostgreSQL</span>'s shared buffers and the
332 portion of the kernel's disk cache that will be used for
333 <span class="productname">PostgreSQL</span> data files, though some
334 data might exist in both places. Also, take
335 into account the expected number of concurrent queries on different
336 tables, since they will have to share the available
337 space. This parameter has no effect on the size of shared
338 memory allocated by <span class="productname">PostgreSQL</span>, nor
339 does it reserve kernel disk cache; it is used only for estimation
340 purposes. The system also does not assume data remains in
341 the disk cache between queries.
342 If this value is specified without units, it is taken as blocks,
343 that is <code class="symbol">BLCKSZ</code> bytes, typically 8kB.
344 The default is 4 gigabytes (<code class="literal">4GB</code>).
345 (If <code class="symbol">BLCKSZ</code> is not 8kB, the default value scales
346 proportionally to it.)
347 </p></dd><dt id="GUC-JIT-ABOVE-COST"><span class="term"><code class="varname">jit_above_cost</code> (<code class="type">floating point</code>)
348 <a id="id-1.6.6.10.3.4.11.1.3" class="indexterm"></a>
349 </span> <a href="#GUC-JIT-ABOVE-COST" class="id_link">#</a></dt><dd><p>
350 Sets the query cost above which JIT compilation is activated, if
351 enabled (see <a class="xref" href="jit.html" title="Chapter 30. Just-in-Time Compilation (JIT)">Chapter 30</a>).
352 Performing <acronym class="acronym">JIT</acronym> costs planning time but can
353 accelerate query execution.
354 Setting this to <code class="literal">-1</code> disables JIT compilation.
355 The default is <code class="literal">100000</code>.
356 </p></dd><dt id="GUC-JIT-INLINE-ABOVE-COST"><span class="term"><code class="varname">jit_inline_above_cost</code> (<code class="type">floating point</code>)
357 <a id="id-1.6.6.10.3.4.12.1.3" class="indexterm"></a>
358 </span> <a href="#GUC-JIT-INLINE-ABOVE-COST" class="id_link">#</a></dt><dd><p>
359 Sets the query cost above which JIT compilation attempts to inline
360 functions and operators. Inlining adds planning time, but can
361 improve execution speed. It is not meaningful to set this to less
362 than <code class="varname">jit_above_cost</code>.
363 Setting this to <code class="literal">-1</code> disables inlining.
364 The default is <code class="literal">500000</code>.
365 </p></dd><dt id="GUC-JIT-OPTIMIZE-ABOVE-COST"><span class="term"><code class="varname">jit_optimize_above_cost</code> (<code class="type">floating point</code>)
366 <a id="id-1.6.6.10.3.4.13.1.3" class="indexterm"></a>
367 </span> <a href="#GUC-JIT-OPTIMIZE-ABOVE-COST" class="id_link">#</a></dt><dd><p>
368 Sets the query cost above which JIT compilation applies expensive
369 optimizations. Such optimization adds planning time, but can improve
370 execution speed. It is not meaningful to set this to less
371 than <code class="varname">jit_above_cost</code>, and it is unlikely to be
372 beneficial to set it to more
373 than <code class="varname">jit_inline_above_cost</code>.
374 Setting this to <code class="literal">-1</code> disables expensive optimizations.
375 The default is <code class="literal">500000</code>.
376 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-QUERY-GEQO"><div class="titlepage"><div><div><h3 class="title">19.7.3. Genetic Query Optimizer <a href="#RUNTIME-CONFIG-QUERY-GEQO" class="id_link">#</a></h3></div></div></div><p>
377 The genetic query optimizer (GEQO) is an algorithm that does query
378 planning using heuristic searching. This reduces planning time for
379 complex queries (those joining many relations), at the cost of producing
380 plans that are sometimes inferior to those found by the normal
381 exhaustive-search algorithm.
382 For more information see <a class="xref" href="geqo.html" title="Chapter 61. Genetic Query Optimizer">Chapter 61</a>.
383 </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-GEQO"><span class="term"><code class="varname">geqo</code> (<code class="type">boolean</code>)
384 <a id="id-1.6.6.10.4.3.1.1.3" class="indexterm"></a>
385 <a id="id-1.6.6.10.4.3.1.1.4" class="indexterm"></a>
386 <a id="id-1.6.6.10.4.3.1.1.5" class="indexterm"></a>
387 </span> <a href="#GUC-GEQO" class="id_link">#</a></dt><dd><p>
388 Enables or disables genetic query optimization.
389 This is on by default. It is usually best not to turn it off in
390 production; the <code class="varname">geqo_threshold</code> variable provides
391 more granular control of GEQO.
392 </p></dd><dt id="GUC-GEQO-THRESHOLD"><span class="term"><code class="varname">geqo_threshold</code> (<code class="type">integer</code>)
393 <a id="id-1.6.6.10.4.3.2.1.3" class="indexterm"></a>
394 </span> <a href="#GUC-GEQO-THRESHOLD" class="id_link">#</a></dt><dd><p>
395 Use genetic query optimization to plan queries with at least
396 this many <code class="literal">FROM</code> items involved. (Note that a
397 <code class="literal">FULL OUTER JOIN</code> construct counts as only one <code class="literal">FROM</code>
398 item.) The default is 12. For simpler queries it is usually best
399 to use the regular, exhaustive-search planner, but for queries with
400 many tables the exhaustive search takes too long, often
401 longer than the penalty of executing a suboptimal plan. Thus,
402 a threshold on the size of the query is a convenient way to manage
404 </p></dd><dt id="GUC-GEQO-EFFORT"><span class="term"><code class="varname">geqo_effort</code> (<code class="type">integer</code>)
405 <a id="id-1.6.6.10.4.3.3.1.3" class="indexterm"></a>
406 </span> <a href="#GUC-GEQO-EFFORT" class="id_link">#</a></dt><dd><p>
407 Controls the trade-off between planning time and query plan
408 quality in GEQO. This variable must be an integer in the
409 range from 1 to 10. The default value is five. Larger values
410 increase the time spent doing query planning, but also
411 increase the likelihood that an efficient query plan will be
414 <code class="varname">geqo_effort</code> doesn't actually do anything
415 directly; it is only used to compute the default values for
416 the other variables that influence GEQO behavior (described
417 below). If you prefer, you can set the other parameters by
419 </p></dd><dt id="GUC-GEQO-POOL-SIZE"><span class="term"><code class="varname">geqo_pool_size</code> (<code class="type">integer</code>)
420 <a id="id-1.6.6.10.4.3.4.1.3" class="indexterm"></a>
421 </span> <a href="#GUC-GEQO-POOL-SIZE" class="id_link">#</a></dt><dd><p>
422 Controls the pool size used by GEQO, that is the
423 number of individuals in the genetic population. It must be
424 at least two, and useful values are typically 100 to 1000. If
425 it is set to zero (the default setting) then a suitable
426 value is chosen based on <code class="varname">geqo_effort</code> and
427 the number of tables in the query.
428 </p></dd><dt id="GUC-GEQO-GENERATIONS"><span class="term"><code class="varname">geqo_generations</code> (<code class="type">integer</code>)
429 <a id="id-1.6.6.10.4.3.5.1.3" class="indexterm"></a>
430 </span> <a href="#GUC-GEQO-GENERATIONS" class="id_link">#</a></dt><dd><p>
431 Controls the number of generations used by GEQO, that is
432 the number of iterations of the algorithm. It must
433 be at least one, and useful values are in the same range as
434 the pool size. If it is set to zero (the default setting)
435 then a suitable value is chosen based on
436 <code class="varname">geqo_pool_size</code>.
437 </p></dd><dt id="GUC-GEQO-SELECTION-BIAS"><span class="term"><code class="varname">geqo_selection_bias</code> (<code class="type">floating point</code>)
438 <a id="id-1.6.6.10.4.3.6.1.3" class="indexterm"></a>
439 </span> <a href="#GUC-GEQO-SELECTION-BIAS" class="id_link">#</a></dt><dd><p>
440 Controls the selection bias used by GEQO. The selection bias
441 is the selective pressure within the population. Values can be
442 from 1.50 to 2.00; the latter is the default.
443 </p></dd><dt id="GUC-GEQO-SEED"><span class="term"><code class="varname">geqo_seed</code> (<code class="type">floating point</code>)
444 <a id="id-1.6.6.10.4.3.7.1.3" class="indexterm"></a>
445 </span> <a href="#GUC-GEQO-SEED" class="id_link">#</a></dt><dd><p>
446 Controls the initial value of the random number generator used
447 by GEQO to select random paths through the join order search space.
448 The value can range from zero (the default) to one. Varying the
449 value changes the set of join paths explored, and may result in a
450 better or worse best path being found.
451 </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-QUERY-OTHER"><div class="titlepage"><div><div><h3 class="title">19.7.4. Other Planner Options <a href="#RUNTIME-CONFIG-QUERY-OTHER" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-DEFAULT-STATISTICS-TARGET"><span class="term"><code class="varname">default_statistics_target</code> (<code class="type">integer</code>)
452 <a id="id-1.6.6.10.5.2.1.1.3" class="indexterm"></a>
453 </span> <a href="#GUC-DEFAULT-STATISTICS-TARGET" class="id_link">#</a></dt><dd><p>
454 Sets the default statistics target for table columns without
455 a column-specific target set via <code class="command">ALTER TABLE
456 SET STATISTICS</code>. Larger values increase the time needed to
457 do <code class="command">ANALYZE</code>, but might improve the quality of the
458 planner's estimates. The default is 100. For more information
459 on the use of statistics by the <span class="productname">PostgreSQL</span>
460 query planner, refer to <a class="xref" href="planner-stats.html" title="14.2. Statistics Used by the Planner">Section 14.2</a>.
461 </p></dd><dt id="GUC-CONSTRAINT-EXCLUSION"><span class="term"><code class="varname">constraint_exclusion</code> (<code class="type">enum</code>)
462 <a id="id-1.6.6.10.5.2.2.1.3" class="indexterm"></a>
463 <a id="id-1.6.6.10.5.2.2.1.4" class="indexterm"></a>
464 </span> <a href="#GUC-CONSTRAINT-EXCLUSION" class="id_link">#</a></dt><dd><p>
465 Controls the query planner's use of table constraints to
467 The allowed values of <code class="varname">constraint_exclusion</code> are
468 <code class="literal">on</code> (examine constraints for all tables),
469 <code class="literal">off</code> (never examine constraints), and
470 <code class="literal">partition</code> (examine constraints only for inheritance
471 child tables and <code class="literal">UNION ALL</code> subqueries).
472 <code class="literal">partition</code> is the default setting.
473 It is often used with traditional inheritance trees to improve
476 When this parameter allows it for a particular table, the planner
477 compares query conditions with the table's <code class="literal">CHECK</code>
478 constraints, and omits scanning tables for which the conditions
479 contradict the constraints. For example:
481 </p><pre class="programlisting">
482 CREATE TABLE parent(key integer, ...);
483 CREATE TABLE child1000(check (key between 1000 and 1999)) INHERITS(parent);
484 CREATE TABLE child2000(check (key between 2000 and 2999)) INHERITS(parent);
486 SELECT * FROM parent WHERE key = 2400;
489 With constraint exclusion enabled, this <code class="command">SELECT</code>
490 will not scan <code class="structname">child1000</code> at all, improving performance.
492 Currently, constraint exclusion is enabled by default
493 only for cases that are often used to implement table partitioning via
494 inheritance trees. Turning it on for all tables imposes extra
495 planning overhead that is quite noticeable on simple queries, and most
496 often will yield no benefit for simple queries. If you have no
497 tables that are partitioned using traditional inheritance, you might
498 prefer to turn it off entirely. (Note that the equivalent feature for
499 partitioned tables is controlled by a separate parameter,
500 <a class="xref" href="runtime-config-query.html#GUC-ENABLE-PARTITION-PRUNING">enable_partition_pruning</a>.)
502 Refer to <a class="xref" href="ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION" title="5.12.5. Partitioning and Constraint Exclusion">Section 5.12.5</a> for
503 more information on using constraint exclusion to implement
505 </p></dd><dt id="GUC-CURSOR-TUPLE-FRACTION"><span class="term"><code class="varname">cursor_tuple_fraction</code> (<code class="type">floating point</code>)
506 <a id="id-1.6.6.10.5.2.3.1.3" class="indexterm"></a>
507 </span> <a href="#GUC-CURSOR-TUPLE-FRACTION" class="id_link">#</a></dt><dd><p>
508 Sets the planner's estimate of the fraction of a cursor's rows that
509 will be retrieved. The default is 0.1. Smaller values of this
510 setting bias the planner towards using <span class="quote">“<span class="quote">fast start</span>”</span> plans
511 for cursors, which will retrieve the first few rows quickly while
512 perhaps taking a long time to fetch all rows. Larger values
513 put more emphasis on the total estimated time. At the maximum
514 setting of 1.0, cursors are planned exactly like regular queries,
515 considering only the total estimated time and not how soon the
516 first rows might be delivered.
517 </p></dd><dt id="GUC-FROM-COLLAPSE-LIMIT"><span class="term"><code class="varname">from_collapse_limit</code> (<code class="type">integer</code>)
518 <a id="id-1.6.6.10.5.2.4.1.3" class="indexterm"></a>
519 </span> <a href="#GUC-FROM-COLLAPSE-LIMIT" class="id_link">#</a></dt><dd><p>
520 The planner will merge sub-queries into upper queries if the
521 resulting <code class="literal">FROM</code> list would have no more than
522 this many items. Smaller values reduce planning time but might
523 yield inferior query plans. The default is eight.
524 For more information see <a class="xref" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Section 14.3</a>.
526 Setting this value to <a class="xref" href="runtime-config-query.html#GUC-GEQO-THRESHOLD">geqo_threshold</a> or more
527 may trigger use of the GEQO planner, resulting in non-optimal
528 plans. See <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO" title="19.7.3. Genetic Query Optimizer">Section 19.7.3</a>.
529 </p></dd><dt id="GUC-JIT"><span class="term"><code class="varname">jit</code> (<code class="type">boolean</code>)
530 <a id="id-1.6.6.10.5.2.5.1.3" class="indexterm"></a>
531 </span> <a href="#GUC-JIT" class="id_link">#</a></dt><dd><p>
532 Determines whether <acronym class="acronym">JIT</acronym> compilation may be used by
533 <span class="productname">PostgreSQL</span>, if available (see <a class="xref" href="jit.html" title="Chapter 30. Just-in-Time Compilation (JIT)">Chapter 30</a>).
534 The default is <code class="literal">on</code>.
535 </p></dd><dt id="GUC-JOIN-COLLAPSE-LIMIT"><span class="term"><code class="varname">join_collapse_limit</code> (<code class="type">integer</code>)
536 <a id="id-1.6.6.10.5.2.6.1.3" class="indexterm"></a>
537 </span> <a href="#GUC-JOIN-COLLAPSE-LIMIT" class="id_link">#</a></dt><dd><p>
538 The planner will rewrite explicit <code class="literal">JOIN</code>
539 constructs (except <code class="literal">FULL JOIN</code>s) into lists of
540 <code class="literal">FROM</code> items whenever a list of no more than this many items
541 would result. Smaller values reduce planning time but might
542 yield inferior query plans.
544 By default, this variable is set the same as
545 <code class="varname">from_collapse_limit</code>, which is appropriate
546 for most uses. Setting it to 1 prevents any reordering of
547 explicit <code class="literal">JOIN</code>s. Thus, the explicit join order
548 specified in the query will be the actual order in which the
549 relations are joined. Because the query planner does not always choose
550 the optimal join order, advanced users can elect to
551 temporarily set this variable to 1, and then specify the join
552 order they desire explicitly.
553 For more information see <a class="xref" href="explicit-joins.html" title="14.3. Controlling the Planner with Explicit JOIN Clauses">Section 14.3</a>.
555 Setting this value to <a class="xref" href="runtime-config-query.html#GUC-GEQO-THRESHOLD">geqo_threshold</a> or more
556 may trigger use of the GEQO planner, resulting in non-optimal
557 plans. See <a class="xref" href="runtime-config-query.html#RUNTIME-CONFIG-QUERY-GEQO" title="19.7.3. Genetic Query Optimizer">Section 19.7.3</a>.
558 </p></dd><dt id="GUC-PLAN-CACHE-MODE"><span class="term"><code class="varname">plan_cache_mode</code> (<code class="type">enum</code>)
559 <a id="id-1.6.6.10.5.2.7.1.3" class="indexterm"></a>
560 </span> <a href="#GUC-PLAN-CACHE-MODE" class="id_link">#</a></dt><dd><p>
561 Prepared statements (either explicitly prepared or implicitly
562 generated, for example by PL/pgSQL) can be executed using custom or
563 generic plans. Custom plans are made afresh for each execution
564 using its specific set of parameter values, while generic plans do
565 not rely on the parameter values and can be re-used across
566 executions. Thus, use of a generic plan saves planning time, but if
567 the ideal plan depends strongly on the parameter values then a
568 generic plan may be inefficient. The choice between these options
569 is normally made automatically, but it can be overridden
570 with <code class="varname">plan_cache_mode</code>.
571 The allowed values are <code class="literal">auto</code> (the default),
572 <code class="literal">force_custom_plan</code> and
573 <code class="literal">force_generic_plan</code>.
574 This setting is considered when a cached plan is to be executed,
575 not when it is prepared.
576 For more information see <a class="xref" href="sql-prepare.html" title="PREPARE"><span class="refentrytitle">PREPARE</span></a>.
577 </p></dd><dt id="GUC-RECURSIVE-WORKTABLE-FACTOR"><span class="term"><code class="varname">recursive_worktable_factor</code> (<code class="type">floating point</code>)
578 <a id="id-1.6.6.10.5.2.8.1.3" class="indexterm"></a>
579 </span> <a href="#GUC-RECURSIVE-WORKTABLE-FACTOR" class="id_link">#</a></dt><dd><p>
580 Sets the planner's estimate of the average size of the working
581 table of a <a class="link" href="queries-with.html#QUERIES-WITH-RECURSIVE" title="7.8.2. Recursive Queries">recursive
582 query</a>, as a multiple of the estimated size of the initial
583 non-recursive term of the query. This helps the planner choose
584 the most appropriate method for joining the working table to the
585 query's other tables.
586 The default value is <code class="literal">10.0</code>. A smaller value
587 such as <code class="literal">1.0</code> can be helpful when the recursion
588 has low <span class="quote">“<span class="quote">fan-out</span>”</span> from one step to the next, as for
589 example in shortest-path queries. Graph analytics queries may
590 benefit from larger-than-default values.
591 </p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-replication.html" title="19.6. Replication">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-logging.html" title="19.8. Error Reporting and Logging">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.6. Replication </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.8. Error Reporting and Logging</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob