| 1 |
<!-- doc/src/sgml/perform.sgml --> |
| 2 |
|
| 3 |
<chapter id="performance-tips"> |
| 4 |
<title>Performance Tips</title> |
| 5 |
|
| 6 |
<indexterm zone="performance-tips"> |
| 7 |
<primary>performance</primary> |
| 8 |
</indexterm> |
| 9 |
|
| 10 |
<para> |
| 11 |
Query performance can be affected by many things. Some of these can |
| 12 |
be controlled by the user, while others are fundamental to the underlying |
| 13 |
design of the system. This chapter provides some hints about understanding |
| 14 |
and tuning <productname>PostgreSQL</productname> performance. |
| 15 |
</para> |
| 16 |
|
| 17 |
<sect1 id="using-explain"> |
| 18 |
<title>Using <command>EXPLAIN</command></title> |
| 19 |
|
| 20 |
<indexterm zone="using-explain"> |
| 21 |
<primary>EXPLAIN</primary> |
| 22 |
</indexterm> |
| 23 |
|
| 24 |
<indexterm zone="using-explain"> |
| 25 |
<primary>query plan</primary> |
| 26 |
</indexterm> |
| 27 |
|
| 28 |
<para> |
| 29 |
<productname>PostgreSQL</productname> devises a <firstterm>query |
| 30 |
plan</firstterm> for each query it receives. Choosing the right |
| 31 |
plan to match the query structure and the properties of the data |
| 32 |
is absolutely critical for good performance, so the system includes |
| 33 |
a complex <firstterm>planner</firstterm> that tries to choose good plans. |
| 34 |
You can use the <link linkend="sql-explain"><command>EXPLAIN</command></link> command |
| 35 |
to see what query plan the planner creates for any query. |
| 36 |
Plan-reading is an art that requires some experience to master, |
| 37 |
but this section attempts to cover the basics. |
| 38 |
</para> |
| 39 |
|
| 40 |
<para> |
| 41 |
Examples in this section are drawn from the regression test database |
| 42 |
after doing a <command>VACUUM ANALYZE</command>, using v18 development sources. |
| 43 |
You should be able to get similar results if you try the examples |
| 44 |
yourself, but your estimated costs and row counts might vary slightly |
| 45 |
because <command>ANALYZE</command>'s statistics are random samples rather |
| 46 |
than exact, and because costs are inherently somewhat platform-dependent. |
| 47 |
</para> |
| 48 |
|
| 49 |
<para> |
| 50 |
The examples use <command>EXPLAIN</command>'s default <quote>text</quote> output |
| 51 |
format, which is compact and convenient for humans to read. |
| 52 |
If you want to feed <command>EXPLAIN</command>'s output to a program for further |
| 53 |
analysis, you should use one of its machine-readable output formats |
| 54 |
(XML, JSON, or YAML) instead. |
| 55 |
</para> |
| 56 |
|
| 57 |
<sect2 id="using-explain-basics"> |
| 58 |
<title><command>EXPLAIN</command> Basics</title> |
| 59 |
|
| 60 |
<para> |
| 61 |
The structure of a query plan is a tree of <firstterm>plan nodes</firstterm>. |
| 62 |
Nodes at the bottom level of the tree are scan nodes: they return raw rows |
| 63 |
from a table. There are different types of scan nodes for different |
| 64 |
table access methods: sequential scans, index scans, and bitmap index |
| 65 |
scans. There are also non-table row sources, such as <literal>VALUES</literal> |
| 66 |
clauses and set-returning functions in <literal>FROM</literal>, which have their |
| 67 |
own scan node types. |
| 68 |
If the query requires joining, aggregation, sorting, or other |
| 69 |
operations on the raw rows, then there will be additional nodes |
| 70 |
above the scan nodes to perform these operations. Again, |
| 71 |
there is usually more than one possible way to do these operations, |
| 72 |
so different node types can appear here too. The output |
| 73 |
of <command>EXPLAIN</command> has one line for each node in the plan |
| 74 |
tree, showing the basic node type plus the cost estimates that the planner |
| 75 |
made for the execution of that plan node. Additional lines might appear, |
| 76 |
indented from the node's summary line, |
| 77 |
to show additional properties of the node. |
| 78 |
The very first line (the summary line for the topmost |
| 79 |
node) has the estimated total execution cost for the plan; it is this |
| 80 |
number that the planner seeks to minimize. |
| 81 |
</para> |
| 82 |
|
| 83 |
<para> |
| 84 |
Here is a trivial example, just to show what the output looks like: |
| 85 |
|
| 86 |
<screen> |
| 87 |
EXPLAIN SELECT * FROM tenk1; |
| 88 |
|
| 89 |
QUERY PLAN |
| 90 |
------------------------------------------------------------- |
| 91 |
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244) |
| 92 |
</screen> |
| 93 |
</para> |
| 94 |
|
| 95 |
<para> |
| 96 |
Since this query has no <literal>WHERE</literal> clause, it must scan all the |
| 97 |
rows of the table, so the planner has chosen to use a simple sequential |
| 98 |
scan plan. The numbers that are quoted in parentheses are (left |
| 99 |
to right): |
| 100 |
|
| 101 |
<itemizedlist> |
| 102 |
<listitem> |
| 103 |
<para> |
| 104 |
Estimated start-up cost. This is the time expended before the output |
| 105 |
phase can begin, e.g., time to do the sorting in a sort node. |
| 106 |
</para> |
| 107 |
</listitem> |
| 108 |
|
| 109 |
<listitem> |
| 110 |
<para> |
| 111 |
Estimated total cost. This is stated on the assumption that the plan |
| 112 |
node is run to completion, i.e., all available rows are retrieved. |
| 113 |
In practice a node's parent node might stop short of reading all |
| 114 |
available rows (see the <literal>LIMIT</literal> example below). |
| 115 |
</para> |
| 116 |
</listitem> |
| 117 |
|
| 118 |
<listitem> |
| 119 |
<para> |
| 120 |
Estimated number of rows output by this plan node. Again, the node |
| 121 |
is assumed to be run to completion. |
| 122 |
</para> |
| 123 |
</listitem> |
| 124 |
|
| 125 |
<listitem> |
| 126 |
<para> |
| 127 |
Estimated average width of rows output by this plan node (in bytes). |
| 128 |
</para> |
| 129 |
</listitem> |
| 130 |
</itemizedlist> |
| 131 |
</para> |
| 132 |
|
| 133 |
<para> |
| 134 |
The costs are measured in arbitrary units determined by the planner's |
| 135 |
cost parameters (see <xref linkend="runtime-config-query-constants"/>). |
| 136 |
Traditional practice is to measure the costs in units of disk page |
| 137 |
fetches; that is, <xref linkend="guc-seq-page-cost"/> is conventionally |
| 138 |
set to <literal>1.0</literal> and the other cost parameters are set relative |
| 139 |
to that. The examples in this section are run with the default cost |
| 140 |
parameters. |
| 141 |
</para> |
| 142 |
|
| 143 |
<para> |
| 144 |
It's important to understand that the cost of an upper-level node includes |
| 145 |
the cost of all its child nodes. It's also important to realize that |
| 146 |
the cost only reflects things that the planner cares about. |
| 147 |
In particular, the cost does not consider the time spent to convert |
| 148 |
output values to text form or to transmit them to the client, which |
| 149 |
could be important factors in the real elapsed time; but the planner |
| 150 |
ignores those costs because it cannot change them by altering the |
| 151 |
plan. (Every correct plan will output the same row set, we trust.) |
| 152 |
</para> |
| 153 |
|
| 154 |
<para> |
| 155 |
The <literal>rows</literal> value is a little tricky because it is |
| 156 |
not the number of rows processed or scanned by the |
| 157 |
plan node, but rather the number emitted by the node. This is often |
| 158 |
less than the number scanned, as a result of filtering by any |
| 159 |
<literal>WHERE</literal>-clause conditions that are being applied at the node. |
| 160 |
Ideally the top-level rows estimate will approximate the number of rows |
| 161 |
actually returned, updated, or deleted by the query. |
| 162 |
</para> |
| 163 |
|
| 164 |
<para> |
| 165 |
Returning to our example: |
| 166 |
|
| 167 |
<screen> |
| 168 |
EXPLAIN SELECT * FROM tenk1; |
| 169 |
|
| 170 |
QUERY PLAN |
| 171 |
------------------------------------------------------------- |
| 172 |
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244) |
| 173 |
</screen> |
| 174 |
</para> |
| 175 |
|
| 176 |
<para> |
| 177 |
These numbers are derived very straightforwardly. If you do: |
| 178 |
|
| 179 |
<programlisting> |
| 180 |
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1'; |
| 181 |
</programlisting> |
| 182 |
|
| 183 |
you will find that <classname>tenk1</classname> has 345 disk |
| 184 |
pages and 10000 rows. The estimated cost is computed as (disk pages read * |
| 185 |
<xref linkend="guc-seq-page-cost"/>) + (rows scanned * |
| 186 |
<xref linkend="guc-cpu-tuple-cost"/>). By default, |
| 187 |
<varname>seq_page_cost</varname> is 1.0 and <varname>cpu_tuple_cost</varname> is 0.01, |
| 188 |
so the estimated cost is (345 * 1.0) + (10000 * 0.01) = 445. |
| 189 |
</para> |
| 190 |
|
| 191 |
<para> |
| 192 |
Now let's modify the query to add a <literal>WHERE</literal> condition: |
| 193 |
|
| 194 |
<screen> |
| 195 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000; |
| 196 |
|
| 197 |
QUERY PLAN |
| 198 |
------------------------------------------------------------ |
| 199 |
Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) |
| 200 |
Filter: (unique1 < 7000) |
| 201 |
</screen> |
| 202 |
|
| 203 |
Notice that the <command>EXPLAIN</command> output shows the <literal>WHERE</literal> |
| 204 |
clause being applied as a <quote>filter</quote> condition attached to the Seq |
| 205 |
Scan plan node. This means that |
| 206 |
the plan node checks the condition for each row it scans, and outputs |
| 207 |
only the ones that pass the condition. |
| 208 |
The estimate of output rows has been reduced because of the |
| 209 |
<literal>WHERE</literal> clause. |
| 210 |
However, the scan will still have to visit all 10000 rows, so the cost |
| 211 |
hasn't decreased; in fact it has gone up a bit (by 10000 * <xref |
| 212 |
linkend="guc-cpu-operator-cost"/>, to be exact) to reflect the extra CPU |
| 213 |
time spent checking the <literal>WHERE</literal> condition. |
| 214 |
</para> |
| 215 |
|
| 216 |
<para> |
| 217 |
The actual number of rows this query would select is 7000, but the <literal>rows</literal> |
| 218 |
estimate is only approximate. If you try to duplicate this experiment, |
| 219 |
you may well get a slightly different estimate; moreover, it can |
| 220 |
change after each <command>ANALYZE</command> command, because the |
| 221 |
statistics produced by <command>ANALYZE</command> are taken from a |
| 222 |
randomized sample of the table. |
| 223 |
</para> |
| 224 |
|
| 225 |
<para> |
| 226 |
Now, let's make the condition more restrictive: |
| 227 |
|
| 228 |
<screen> |
| 229 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100; |
| 230 |
|
| 231 |
QUERY PLAN |
| 232 |
-------------------------------------------------------------------&zwsp;----------- |
| 233 |
Bitmap Heap Scan on tenk1 (cost=5.06..224.98 rows=100 width=244) |
| 234 |
Recheck Cond: (unique1 < 100) |
| 235 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) |
| 236 |
Index Cond: (unique1 < 100) |
| 237 |
</screen> |
| 238 |
|
| 239 |
Here the planner has decided to use a two-step plan: the child plan |
| 240 |
node visits an index to find the locations of rows matching the index |
| 241 |
condition, and then the upper plan node actually fetches those rows |
| 242 |
from the table itself. Fetching rows separately is much more |
| 243 |
expensive than reading them sequentially, but because not all the pages |
| 244 |
of the table have to be visited, this is still cheaper than a sequential |
| 245 |
scan. (The reason for using two plan levels is that the upper plan |
| 246 |
node sorts the row locations identified by the index into physical order |
| 247 |
before reading them, to minimize the cost of separate fetches. |
| 248 |
The <quote>bitmap</quote> mentioned in the node names is the mechanism that |
| 249 |
does the sorting.) |
| 250 |
</para> |
| 251 |
|
| 252 |
<para> |
| 253 |
Now let's add another condition to the <literal>WHERE</literal> clause: |
| 254 |
|
| 255 |
<screen> |
| 256 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx'; |
| 257 |
|
| 258 |
QUERY PLAN |
| 259 |
-------------------------------------------------------------------&zwsp;----------- |
| 260 |
Bitmap Heap Scan on tenk1 (cost=5.04..225.20 rows=1 width=244) |
| 261 |
Recheck Cond: (unique1 < 100) |
| 262 |
Filter: (stringu1 = 'xxx'::name) |
| 263 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) |
| 264 |
Index Cond: (unique1 < 100) |
| 265 |
</screen> |
| 266 |
|
| 267 |
The added condition <literal>stringu1 = 'xxx'</literal> reduces the |
| 268 |
output row count estimate, but not the cost because we still have to visit |
| 269 |
the same set of rows. That's because the <literal>stringu1</literal> clause |
| 270 |
cannot be applied as an index condition, since this index is only on |
| 271 |
the <literal>unique1</literal> column. Instead it is applied as a filter on |
| 272 |
the rows retrieved using the index. Thus the cost has actually gone up |
| 273 |
slightly to reflect this extra checking. |
| 274 |
</para> |
| 275 |
|
| 276 |
<para> |
| 277 |
In some cases the planner will prefer a <quote>simple</quote> index scan plan: |
| 278 |
|
| 279 |
<screen> |
| 280 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42; |
| 281 |
|
| 282 |
QUERY PLAN |
| 283 |
-------------------------------------------------------------------&zwsp;---------- |
| 284 |
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244) |
| 285 |
Index Cond: (unique1 = 42) |
| 286 |
</screen> |
| 287 |
|
| 288 |
In this type of plan the table rows are fetched in index order, which |
| 289 |
makes them even more expensive to read, but there are so few that the |
| 290 |
extra cost of sorting the row locations is not worth it. You'll most |
| 291 |
often see this plan type for queries that fetch just a single row. It's |
| 292 |
also often used for queries that have an <literal>ORDER BY</literal> condition |
| 293 |
that matches the index order, because then no extra sorting step is needed |
| 294 |
to satisfy the <literal>ORDER BY</literal>. In this example, adding |
| 295 |
<literal>ORDER BY unique1</literal> would use the same plan because the |
| 296 |
index already implicitly provides the requested ordering. |
| 297 |
</para> |
| 298 |
|
| 299 |
<para> |
| 300 |
The planner may implement an <literal>ORDER BY</literal> clause in several |
| 301 |
ways. The above example shows that such an ordering clause may be |
| 302 |
implemented implicitly. The planner may also add an explicit |
| 303 |
<literal>Sort</literal> step: |
| 304 |
|
| 305 |
<screen> |
| 306 |
EXPLAIN SELECT * FROM tenk1 ORDER BY unique1; |
| 307 |
|
| 308 |
QUERY PLAN |
| 309 |
------------------------------------------------------------------- |
| 310 |
Sort (cost=1109.39..1134.39 rows=10000 width=244) |
| 311 |
Sort Key: unique1 |
| 312 |
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244) |
| 313 |
</screen> |
| 314 |
|
| 315 |
If a part of the plan guarantees an ordering on a prefix of the |
| 316 |
required sort keys, then the planner may instead decide to use an |
| 317 |
<literal>Incremental Sort</literal> step: |
| 318 |
|
| 319 |
<screen> |
| 320 |
EXPLAIN SELECT * FROM tenk1 ORDER BY hundred, ten LIMIT 100; |
| 321 |
|
| 322 |
QUERY PLAN |
| 323 |
-------------------------------------------------------------------&zwsp;----------------------------- |
| 324 |
Limit (cost=19.35..39.49 rows=100 width=244) |
| 325 |
-> Incremental Sort (cost=19.35..2033.39 rows=10000 width=244) |
| 326 |
Sort Key: hundred, ten |
| 327 |
Presorted Key: hundred |
| 328 |
-> Index Scan using tenk1_hundred on tenk1 (cost=0.29..1574.20 rows=10000 width=244) |
| 329 |
</screen> |
| 330 |
|
| 331 |
Compared to regular sorts, sorting incrementally allows returning tuples |
| 332 |
before the entire result set has been sorted, which particularly enables |
| 333 |
optimizations with <literal>LIMIT</literal> queries. It may also reduce |
| 334 |
memory usage and the likelihood of spilling sorts to disk, but it comes at |
| 335 |
the cost of the increased overhead of splitting the result set into multiple |
| 336 |
sorting batches. |
| 337 |
</para> |
| 338 |
|
| 339 |
<para> |
| 340 |
If there are separate indexes on several of the columns referenced |
| 341 |
in <literal>WHERE</literal>, the planner might choose to use an AND or OR |
| 342 |
combination of the indexes: |
| 343 |
|
| 344 |
<screen> |
| 345 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; |
| 346 |
|
| 347 |
QUERY PLAN |
| 348 |
-------------------------------------------------------------------&zwsp;------------------ |
| 349 |
Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) |
| 350 |
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) |
| 351 |
-> BitmapAnd (cost=25.07..25.07 rows=10 width=0) |
| 352 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) |
| 353 |
Index Cond: (unique1 < 100) |
| 354 |
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) |
| 355 |
Index Cond: (unique2 > 9000) |
| 356 |
</screen> |
| 357 |
|
| 358 |
But this requires visiting both indexes, so it's not necessarily a win |
| 359 |
compared to using just one index and treating the other condition as |
| 360 |
a filter. If you vary the ranges involved you'll see the plan change |
| 361 |
accordingly. |
| 362 |
</para> |
| 363 |
|
| 364 |
<para> |
| 365 |
Here is an example showing the effects of <literal>LIMIT</literal>: |
| 366 |
|
| 367 |
<screen> |
| 368 |
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; |
| 369 |
|
| 370 |
QUERY PLAN |
| 371 |
-------------------------------------------------------------------&zwsp;------------------ |
| 372 |
Limit (cost=0.29..14.28 rows=2 width=244) |
| 373 |
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.27 rows=10 width=244) |
| 374 |
Index Cond: (unique2 > 9000) |
| 375 |
Filter: (unique1 < 100) |
| 376 |
</screen> |
| 377 |
</para> |
| 378 |
|
| 379 |
<para> |
| 380 |
This is the same query as above, but we added a <literal>LIMIT</literal> so that |
| 381 |
not all the rows need be retrieved, and the planner changed its mind about |
| 382 |
what to do. Notice that the total cost and row count of the Index Scan |
| 383 |
node are shown as if it were run to completion. However, the Limit node |
| 384 |
is expected to stop after retrieving only a fifth of those rows, so its |
| 385 |
total cost is only a fifth as much, and that's the actual estimated cost |
| 386 |
of the query. This plan is preferred over adding a Limit node to the |
| 387 |
previous plan because the Limit could not avoid paying the startup cost |
| 388 |
of the bitmap scan, so the total cost would be something over 25 units |
| 389 |
with that approach. |
| 390 |
</para> |
| 391 |
|
| 392 |
<para> |
| 393 |
Let's try joining two tables, using the columns we have been discussing: |
| 394 |
|
| 395 |
<screen> |
| 396 |
EXPLAIN SELECT * |
| 397 |
FROM tenk1 t1, tenk2 t2 |
| 398 |
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; |
| 399 |
|
| 400 |
QUERY PLAN |
| 401 |
-------------------------------------------------------------------&zwsp;------------------- |
| 402 |
Nested Loop (cost=4.65..118.50 rows=10 width=488) |
| 403 |
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) |
| 404 |
Recheck Cond: (unique1 < 10) |
| 405 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) |
| 406 |
Index Cond: (unique1 < 10) |
| 407 |
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) |
| 408 |
Index Cond: (unique2 = t1.unique2) |
| 409 |
</screen> |
| 410 |
</para> |
| 411 |
|
| 412 |
<para> |
| 413 |
In this plan, we have a nested-loop join node with two table scans as |
| 414 |
inputs, or children. The indentation of the node summary lines reflects |
| 415 |
the plan tree structure. The join's first, or <quote>outer</quote>, child |
| 416 |
is a bitmap scan similar to those we saw before. Its cost and row count |
| 417 |
are the same as we'd get from <literal>SELECT ... WHERE unique1 < 10</literal> |
| 418 |
because we are |
| 419 |
applying the <literal>WHERE</literal> clause <literal>unique1 < 10</literal> |
| 420 |
at that node. |
| 421 |
The <literal>t1.unique2 = t2.unique2</literal> clause is not relevant yet, |
| 422 |
so it doesn't affect the row count of the outer scan. The nested-loop |
| 423 |
join node will run its second, |
| 424 |
or <quote>inner</quote> child once for each row obtained from the outer child. |
| 425 |
Column values from the current outer row can be plugged into the inner |
| 426 |
scan; here, the <literal>t1.unique2</literal> value from the outer row is available, |
| 427 |
so we get a plan and costs similar to what we saw above for a simple |
| 428 |
<literal>SELECT ... WHERE t2.unique2 = <replaceable>constant</replaceable></literal> case. |
| 429 |
(The estimated cost is actually a bit lower than what was seen above, |
| 430 |
as a result of caching that's expected to occur during the repeated |
| 431 |
index scans on <literal>t2</literal>.) The |
| 432 |
costs of the loop node are then set on the basis of the cost of the outer |
| 433 |
scan, plus one repetition of the inner scan for each outer row (10 * 7.90, |
| 434 |
here), plus a little CPU time for join processing. |
| 435 |
</para> |
| 436 |
|
| 437 |
<para> |
| 438 |
In this example the join's output row count is the same as the product |
| 439 |
of the two scans' row counts, but that's not true in all cases because |
| 440 |
there can be additional <literal>WHERE</literal> clauses that mention both tables |
| 441 |
and so can only be applied at the join point, not to either input scan. |
| 442 |
Here's an example: |
| 443 |
|
| 444 |
<screen> |
| 445 |
EXPLAIN SELECT * |
| 446 |
FROM tenk1 t1, tenk2 t2 |
| 447 |
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred; |
| 448 |
|
| 449 |
QUERY PLAN |
| 450 |
-------------------------------------------------------------------&zwsp;-------------------------- |
| 451 |
Nested Loop (cost=4.65..49.36 rows=33 width=488) |
| 452 |
Join Filter: (t1.hundred < t2.hundred) |
| 453 |
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) |
| 454 |
Recheck Cond: (unique1 < 10) |
| 455 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) |
| 456 |
Index Cond: (unique1 < 10) |
| 457 |
-> Materialize (cost=0.29..8.51 rows=10 width=244) |
| 458 |
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244) |
| 459 |
Index Cond: (unique2 < 10) |
| 460 |
</screen> |
| 461 |
|
| 462 |
The condition <literal>t1.hundred < t2.hundred</literal> can't be |
| 463 |
tested in the <literal>tenk2_unique2</literal> index, so it's applied at the |
| 464 |
join node. This reduces the estimated output row count of the join node, |
| 465 |
but does not change either input scan. |
| 466 |
</para> |
| 467 |
|
| 468 |
<para> |
| 469 |
Notice that here the planner has chosen to <quote>materialize</quote> the inner |
| 470 |
relation of the join, by putting a Materialize plan node atop it. This |
| 471 |
means that the <literal>t2</literal> index scan will be done just once, even |
| 472 |
though the nested-loop join node needs to read that data ten times, once |
| 473 |
for each row from the outer relation. The Materialize node saves the data |
| 474 |
in memory as it's read, and then returns the data from memory on each |
| 475 |
subsequent pass. |
| 476 |
</para> |
| 477 |
|
| 478 |
<para> |
| 479 |
When dealing with outer joins, you might see join plan nodes with both |
| 480 |
<quote>Join Filter</quote> and plain <quote>Filter</quote> conditions attached. |
| 481 |
Join Filter conditions come from the outer join's <literal>ON</literal> clause, |
| 482 |
so a row that fails the Join Filter condition could still get emitted as |
| 483 |
a null-extended row. But a plain Filter condition is applied after the |
| 484 |
outer-join rules and so acts to remove rows unconditionally. In an inner |
| 485 |
join there is no semantic difference between these types of filters. |
| 486 |
</para> |
| 487 |
|
| 488 |
<para> |
| 489 |
If we change the query's selectivity a bit, we might get a very different |
| 490 |
join plan: |
| 491 |
|
| 492 |
<screen> |
| 493 |
EXPLAIN SELECT * |
| 494 |
FROM tenk1 t1, tenk2 t2 |
| 495 |
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; |
| 496 |
|
| 497 |
QUERY PLAN |
| 498 |
-------------------------------------------------------------------&zwsp;----------------------- |
| 499 |
Hash Join (cost=226.23..709.73 rows=100 width=488) |
| 500 |
Hash Cond: (t2.unique2 = t1.unique2) |
| 501 |
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) |
| 502 |
-> Hash (cost=224.98..224.98 rows=100 width=244) |
| 503 |
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) |
| 504 |
Recheck Cond: (unique1 < 100) |
| 505 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) |
| 506 |
Index Cond: (unique1 < 100) |
| 507 |
</screen> |
| 508 |
</para> |
| 509 |
|
| 510 |
<para> |
| 511 |
Here, the planner has chosen to use a hash join, in which rows of one |
| 512 |
table are entered into an in-memory hash table, after which the other |
| 513 |
table is scanned and the hash table is probed for matches to each row. |
| 514 |
Again note how the indentation reflects the plan structure: the bitmap |
| 515 |
scan on <literal>tenk1</literal> is the input to the Hash node, which constructs |
| 516 |
the hash table. That's then returned to the Hash Join node, which reads |
| 517 |
rows from its outer child plan and searches the hash table for each one. |
| 518 |
</para> |
| 519 |
|
| 520 |
<para> |
| 521 |
Another possible type of join is a merge join, illustrated here: |
| 522 |
|
| 523 |
<screen> |
| 524 |
EXPLAIN SELECT * |
| 525 |
FROM tenk1 t1, onek t2 |
| 526 |
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; |
| 527 |
|
| 528 |
QUERY PLAN |
| 529 |
-------------------------------------------------------------------&zwsp;----------------------- |
| 530 |
Merge Join (cost=0.56..233.49 rows=10 width=488) |
| 531 |
Merge Cond: (t1.unique2 = t2.unique2) |
| 532 |
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..643.28 rows=100 width=244) |
| 533 |
Filter: (unique1 < 100) |
| 534 |
-> Index Scan using onek_unique2 on onek t2 (cost=0.28..166.28 rows=1000 width=244) |
| 535 |
</screen> |
| 536 |
</para> |
| 537 |
|
| 538 |
<para> |
| 539 |
Merge join requires its input data to be sorted on the join keys. In this |
| 540 |
example each input is sorted by using an index scan to visit the rows |
| 541 |
in the correct order; but a sequential scan and sort could also be used. |
| 542 |
(Sequential-scan-and-sort frequently beats an index scan for sorting many rows, |
| 543 |
because of the nonsequential disk access required by the index scan.) |
| 544 |
</para> |
| 545 |
|
| 546 |
<para> |
| 547 |
One way to look at variant plans is to force the planner to disregard |
| 548 |
whatever strategy it thought was the cheapest, using the enable/disable |
| 549 |
flags described in <xref linkend="runtime-config-query-enable"/>. |
| 550 |
(This is a crude tool, but useful. See |
| 551 |
also <xref linkend="explicit-joins"/>.) |
| 552 |
For example, if we're unconvinced that merge join is the best join |
| 553 |
type for the previous example, we could try |
| 554 |
|
| 555 |
<screen> |
| 556 |
SET enable_mergejoin = off; |
| 557 |
|
| 558 |
EXPLAIN SELECT * |
| 559 |
FROM tenk1 t1, onek t2 |
| 560 |
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2; |
| 561 |
|
| 562 |
QUERY PLAN |
| 563 |
-------------------------------------------------------------------&zwsp;----------------------- |
| 564 |
Hash Join (cost=226.23..344.08 rows=10 width=488) |
| 565 |
Hash Cond: (t2.unique2 = t1.unique2) |
| 566 |
-> Seq Scan on onek t2 (cost=0.00..114.00 rows=1000 width=244) |
| 567 |
-> Hash (cost=224.98..224.98 rows=100 width=244) |
| 568 |
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) |
| 569 |
Recheck Cond: (unique1 < 100) |
| 570 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) |
| 571 |
Index Cond: (unique1 < 100) |
| 572 |
</screen> |
| 573 |
|
| 574 |
which shows that the planner thinks that hash join would be nearly 50% |
| 575 |
more expensive than merge join for this case. |
| 576 |
Of course, the next question is whether it's right about that. |
| 577 |
We can investigate that using <command>EXPLAIN ANALYZE</command>, as |
| 578 |
discussed <link linkend="using-explain-analyze">below</link>. |
| 579 |
</para> |
| 580 |
|
| 581 |
<para> |
| 582 |
When using the enable/disable flags to disable plan node types, many of |
| 583 |
the flags only discourage the use of the corresponding plan node and don't |
| 584 |
outright disallow the planner's ability to use the plan node type. This |
| 585 |
is by design so that the planner still maintains the ability to form a |
| 586 |
plan for a given query. When the resulting plan contains a disabled node, |
| 587 |
the <command>EXPLAIN</command> output will indicate this fact. |
| 588 |
|
| 589 |
<screen> |
| 590 |
SET enable_seqscan = off; |
| 591 |
EXPLAIN SELECT * FROM unit; |
| 592 |
|
| 593 |
QUERY PLAN |
| 594 |
--------------------------------------------------------- |
| 595 |
Seq Scan on unit (cost=0.00..21.30 rows=1130 width=44) |
| 596 |
Disabled: true |
| 597 |
</screen> |
| 598 |
</para> |
| 599 |
|
| 600 |
<para> |
| 601 |
Because the <literal>unit</literal> table has no indexes, there is no |
| 602 |
other means to read the table data, so the sequential scan is the only |
| 603 |
option available to the query planner. |
| 604 |
</para> |
| 605 |
|
| 606 |
<para> |
| 607 |
<indexterm> |
| 608 |
<primary>subplan</primary> |
| 609 |
</indexterm> |
| 610 |
Some query plans involve <firstterm>subplans</firstterm>, which arise |
| 611 |
from sub-<literal>SELECT</literal>s in the original query. Such |
| 612 |
queries can sometimes be transformed into ordinary join plans, but |
| 613 |
when they cannot be, we get plans like: |
| 614 |
|
| 615 |
<screen> |
| 616 |
EXPLAIN VERBOSE SELECT unique1 |
| 617 |
FROM tenk1 t |
| 618 |
WHERE t.ten < ALL (SELECT o.ten FROM onek o WHERE o.four = t.four); |
| 619 |
|
| 620 |
QUERY PLAN |
| 621 |
-------------------------------------------------------------------&zwsp;------ |
| 622 |
Seq Scan on public.tenk1 t (cost=0.00..586095.00 rows=5000 width=4) |
| 623 |
Output: t.unique1 |
| 624 |
Filter: (ALL (t.ten < (SubPlan 1).col1)) |
| 625 |
SubPlan 1 |
| 626 |
-> Seq Scan on public.onek o (cost=0.00..116.50 rows=250 width=4) |
| 627 |
Output: o.ten |
| 628 |
Filter: (o.four = t.four) |
| 629 |
</screen> |
| 630 |
|
| 631 |
This rather artificial example serves to illustrate a couple of |
| 632 |
points: values from the outer plan level can be passed down into a |
| 633 |
subplan (here, <literal>t.four</literal> is passed down) and the |
| 634 |
results of the sub-select are available to the outer plan. Those |
| 635 |
result values are shown by <command>EXPLAIN</command> with notations |
| 636 |
like |
| 637 |
<literal>(<replaceable>subplan_name</replaceable>).col<replaceable>N</replaceable></literal>, |
| 638 |
which refers to the <replaceable>N</replaceable>'th output column of |
| 639 |
the sub-<literal>SELECT</literal>. |
| 640 |
</para> |
| 641 |
|
| 642 |
<para> |
| 643 |
<indexterm> |
| 644 |
<primary>subplan</primary> |
| 645 |
<secondary>hashed</secondary> |
| 646 |
</indexterm> |
| 647 |
In the example above, the <literal>ALL</literal> operator runs the |
| 648 |
subplan again for each row of the outer query (which accounts for the |
| 649 |
high estimated cost). Some queries can use a <firstterm>hashed |
| 650 |
subplan</firstterm> to avoid that: |
| 651 |
|
| 652 |
<screen> |
| 653 |
EXPLAIN SELECT * |
| 654 |
FROM tenk1 t |
| 655 |
WHERE t.unique1 NOT IN (SELECT o.unique1 FROM onek o); |
| 656 |
|
| 657 |
QUERY PLAN |
| 658 |
-------------------------------------------------------------------&zwsp;------------------------- |
| 659 |
Seq Scan on tenk1 t (cost=61.77..531.77 rows=5000 width=244) |
| 660 |
Filter: (NOT (ANY (unique1 = (hashed SubPlan 1).col1))) |
| 661 |
SubPlan 1 |
| 662 |
-> Index Only Scan using onek_unique1 on onek o (cost=0.28..59.27 rows=1000 width=4) |
| 663 |
(4 rows) |
| 664 |
</screen> |
| 665 |
|
| 666 |
Here, the subplan is run a single time and its output is loaded into |
| 667 |
an in-memory hash table, which is then probed by the |
| 668 |
outer <literal>ANY</literal> operator. This requires that the |
| 669 |
sub-<literal>SELECT</literal> not reference any variables of the outer |
| 670 |
query, and that the <literal>ANY</literal>'s comparison operator be |
| 671 |
amenable to hashing. |
| 672 |
</para> |
| 673 |
|
| 674 |
<para> |
| 675 |
<indexterm> |
| 676 |
<primary>initplan</primary> |
| 677 |
</indexterm> |
| 678 |
If, in addition to not referencing any variables of the outer query, |
| 679 |
the sub-<literal>SELECT</literal> cannot return more than one row, |
| 680 |
it may instead be implemented as an <firstterm>initplan</firstterm>: |
| 681 |
|
| 682 |
<screen> |
| 683 |
EXPLAIN VERBOSE SELECT unique1 |
| 684 |
FROM tenk1 t1 WHERE t1.ten = (SELECT (random() * 10)::integer); |
| 685 |
|
| 686 |
QUERY PLAN |
| 687 |
------------------------------------------------------------&zwsp;-------- |
| 688 |
Seq Scan on public.tenk1 t1 (cost=0.02..470.02 rows=1000 width=4) |
| 689 |
Output: t1.unique1 |
| 690 |
Filter: (t1.ten = (InitPlan 1).col1) |
| 691 |
InitPlan 1 |
| 692 |
-> Result (cost=0.00..0.02 rows=1 width=4) |
| 693 |
Output: ((random() * '10'::double precision))::integer |
| 694 |
</screen> |
| 695 |
|
| 696 |
An initplan is run only once per execution of the outer plan, and its |
| 697 |
results are saved for re-use in later rows of the outer plan. So in |
| 698 |
this example <literal>random()</literal> is evaluated only once and |
| 699 |
all the values of <literal>t1.ten</literal> are compared to the same |
| 700 |
randomly-chosen integer. That's quite different from what would |
| 701 |
happen without the sub-<literal>SELECT</literal> construct. |
| 702 |
</para> |
| 703 |
|
| 704 |
</sect2> |
| 705 |
|
| 706 |
<sect2 id="using-explain-analyze"> |
| 707 |
<title><command>EXPLAIN ANALYZE</command></title> |
| 708 |
|
| 709 |
<para> |
| 710 |
It is possible to check the accuracy of the planner's estimates |
| 711 |
by using <command>EXPLAIN</command>'s <literal>ANALYZE</literal> option. With this |
| 712 |
option, <command>EXPLAIN</command> actually executes the query, and then displays |
| 713 |
the true row counts and true run time accumulated within each plan node, |
| 714 |
along with the same estimates that a plain <command>EXPLAIN</command> |
| 715 |
shows. For example, we might get a result like this: |
| 716 |
|
| 717 |
<screen> |
| 718 |
EXPLAIN ANALYZE SELECT * |
| 719 |
FROM tenk1 t1, tenk2 t2 |
| 720 |
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2; |
| 721 |
|
| 722 |
QUERY PLAN |
| 723 |
-------------------------------------------------------------------&zwsp;-------------------------------------------------------------- |
| 724 |
Nested Loop (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1) |
| 725 |
Buffers: shared hit=36 read=6 |
| 726 |
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 loops=1) |
| 727 |
Recheck Cond: (unique1 < 10) |
| 728 |
Heap Blocks: exact=10 |
| 729 |
Buffers: shared hit=3 read=5 written=4 |
| 730 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1) |
| 731 |
Index Cond: (unique1 < 10) |
| 732 |
Index Searches: 1 |
| 733 |
Buffers: shared hit=2 |
| 734 |
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10) |
| 735 |
Index Cond: (unique2 = t1.unique2) |
| 736 |
Index Searches: 10 |
| 737 |
Buffers: shared hit=24 read=6 |
| 738 |
Planning: |
| 739 |
Buffers: shared hit=15 dirtied=9 |
| 740 |
Planning Time: 0.485 ms |
| 741 |
Execution Time: 0.073 ms |
| 742 |
</screen> |
| 743 |
|
| 744 |
Note that the <quote>actual time</quote> values are in milliseconds of |
| 745 |
real time, whereas the <literal>cost</literal> estimates are expressed in |
| 746 |
arbitrary units; so they are unlikely to match up. |
| 747 |
The thing that's usually most important to look for is whether the |
| 748 |
estimated row counts are reasonably close to reality. In this example |
| 749 |
the estimates were all dead-on, but that's quite unusual in practice. |
| 750 |
</para> |
| 751 |
|
| 752 |
<para> |
| 753 |
In some query plans, it is possible for a subplan node to be executed more |
| 754 |
than once. For example, the inner index scan will be executed once per |
| 755 |
outer row in the above nested-loop plan. In such cases, the |
| 756 |
<literal>loops</literal> value reports the |
| 757 |
total number of executions of the node, and the actual time and rows |
| 758 |
values shown are averages per-execution. This is done to make the numbers |
| 759 |
comparable with the way that the cost estimates are shown. Multiply by |
| 760 |
the <literal>loops</literal> value to get the total time actually spent in |
| 761 |
the node. In the above example, we spent a total of 0.030 milliseconds |
| 762 |
executing the index scans on <literal>tenk2</literal>. |
| 763 |
</para> |
| 764 |
|
| 765 |
<para> |
| 766 |
In some cases <command>EXPLAIN ANALYZE</command> shows additional execution |
| 767 |
statistics beyond the plan node execution times and row counts. |
| 768 |
For example, Sort and Hash nodes provide extra information: |
| 769 |
|
| 770 |
<screen> |
| 771 |
EXPLAIN ANALYZE SELECT * |
| 772 |
FROM tenk1 t1, tenk2 t2 |
| 773 |
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous; |
| 774 |
|
| 775 |
QUERY PLAN |
| 776 |
-------------------------------------------------------------------&zwsp;-------------------------------------------------------------------&zwsp;------ |
| 777 |
Sort (cost=713.05..713.30 rows=100 width=488) (actual time=2.995..3.002 rows=100.00 loops=1) |
| 778 |
Sort Key: t1.fivethous |
| 779 |
Sort Method: quicksort Memory: 74kB |
| 780 |
Buffers: shared hit=440 |
| 781 |
-> Hash Join (cost=226.23..709.73 rows=100 width=488) (actual time=0.515..2.920 rows=100.00 loops=1) |
| 782 |
Hash Cond: (t2.unique2 = t1.unique2) |
| 783 |
Buffers: shared hit=437 |
| 784 |
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.026..1.790 rows=10000.00 loops=1) |
| 785 |
Buffers: shared hit=345 |
| 786 |
-> Hash (cost=224.98..224.98 rows=100 width=244) (actual time=0.476..0.477 rows=100.00 loops=1) |
| 787 |
Buckets: 1024 Batches: 1 Memory Usage: 35kB |
| 788 |
Buffers: shared hit=92 |
| 789 |
-> Bitmap Heap Scan on tenk1 t1 (cost=5.06..224.98 rows=100 width=244) (actual time=0.030..0.450 rows=100.00 loops=1) |
| 790 |
Recheck Cond: (unique1 < 100) |
| 791 |
Heap Blocks: exact=90 |
| 792 |
Buffers: shared hit=92 |
| 793 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.013..0.013 rows=100.00 loops=1) |
| 794 |
Index Cond: (unique1 < 100) |
| 795 |
Index Searches: 1 |
| 796 |
Buffers: shared hit=2 |
| 797 |
Planning: |
| 798 |
Buffers: shared hit=12 |
| 799 |
Planning Time: 0.187 ms |
| 800 |
Execution Time: 3.036 ms |
| 801 |
</screen> |
| 802 |
|
| 803 |
The Sort node shows the sort method used (in particular, whether the sort |
| 804 |
was in-memory or on-disk) and the amount of memory or disk space needed. |
| 805 |
The Hash node shows the number of hash buckets and batches as well as the |
| 806 |
peak amount of memory used for the hash table. (If the number of batches |
| 807 |
exceeds one, there will also be disk space usage involved, but that is not |
| 808 |
shown.) |
| 809 |
</para> |
| 810 |
|
| 811 |
<para> |
| 812 |
Index Scan nodes (as well as Bitmap Index Scan and Index-Only Scan nodes) |
| 813 |
show an <quote>Index Searches</quote> line that reports the total number |
| 814 |
of searches across <emphasis>all</emphasis> node |
| 815 |
executions/<literal>loops</literal>: |
| 816 |
|
| 817 |
<screen> |
| 818 |
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 500, 700, 999); |
| 819 |
QUERY PLAN |
| 820 |
-------------------------------------------------------------------&zwsp;--------------------------------------------------------------- |
| 821 |
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.012..0.028 rows=40.00 loops=1) |
| 822 |
Recheck Cond: (thousand = ANY ('{1,500,700,999}'::integer[])) |
| 823 |
Heap Blocks: exact=39 |
| 824 |
Buffers: shared hit=47 |
| 825 |
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.009..0.009 rows=40.00 loops=1) |
| 826 |
Index Cond: (thousand = ANY ('{1,500,700,999}'::integer[])) |
| 827 |
Index Searches: 4 |
| 828 |
Buffers: shared hit=8 |
| 829 |
Planning Time: 0.029 ms |
| 830 |
Execution Time: 0.034 ms |
| 831 |
</screen> |
| 832 |
|
| 833 |
Here we see a Bitmap Index Scan node that needed 4 separate index |
| 834 |
searches. The scan had to search the index from the |
| 835 |
<structname>tenk1_thous_tenthous</structname> index root page once per |
| 836 |
<type>integer</type> value from the predicate's <literal>IN</literal> |
| 837 |
construct. However, the number of index searches often won't have such a |
| 838 |
simple correspondence to the query predicate: |
| 839 |
|
| 840 |
<screen> |
| 841 |
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE thousand IN (1, 2, 3, 4); |
| 842 |
QUERY PLAN |
| 843 |
-------------------------------------------------------------------&zwsp;--------------------------------------------------------------- |
| 844 |
Bitmap Heap Scan on tenk1 (cost=9.45..73.44 rows=40 width=244) (actual time=0.009..0.019 rows=40.00 loops=1) |
| 845 |
Recheck Cond: (thousand = ANY ('{1,2,3,4}'::integer[])) |
| 846 |
Heap Blocks: exact=38 |
| 847 |
Buffers: shared hit=40 |
| 848 |
-> Bitmap Index Scan on tenk1_thous_tenthous (cost=0.00..9.44 rows=40 width=0) (actual time=0.005..0.005 rows=40.00 loops=1) |
| 849 |
Index Cond: (thousand = ANY ('{1,2,3,4}'::integer[])) |
| 850 |
Index Searches: 1 |
| 851 |
Buffers: shared hit=2 |
| 852 |
Planning Time: 0.029 ms |
| 853 |
Execution Time: 0.026 ms |
| 854 |
</screen> |
| 855 |
|
| 856 |
This variant of our <literal>IN</literal> query performed only 1 index |
| 857 |
search. It spent less time traversing the index (compared to the original |
| 858 |
query) because its <literal>IN</literal> construct uses values matching |
| 859 |
index tuples stored next to each other, on the same |
| 860 |
<structname>tenk1_thous_tenthous</structname> index leaf page. |
| 861 |
</para> |
| 862 |
|
| 863 |
<para> |
| 864 |
The <quote>Index Searches</quote> line is also useful with B-tree index |
| 865 |
scans that apply the <firstterm>skip scan</firstterm> optimization to |
| 866 |
more efficiently traverse through an index: |
| 867 |
<screen> |
| 868 |
EXPLAIN ANALYZE SELECT four, unique1 FROM tenk1 WHERE four BETWEEN 1 AND 3 AND unique1 = 42; |
| 869 |
QUERY PLAN |
| 870 |
-------------------------------------------------------------------&zwsp;--------------------------------------------------------------- |
| 871 |
Index Only Scan using tenk1_four_unique1_idx on tenk1 (cost=0.29..6.90 rows=1 width=8) (actual time=0.006..0.007 rows=1.00 loops=1) |
| 872 |
Index Cond: ((four >= 1) AND (four <= 3) AND (unique1 = 42)) |
| 873 |
Heap Fetches: 0 |
| 874 |
Index Searches: 3 |
| 875 |
Buffers: shared hit=7 |
| 876 |
Planning Time: 0.029 ms |
| 877 |
Execution Time: 0.012 ms |
| 878 |
</screen> |
| 879 |
|
| 880 |
Here we see an Index-Only Scan node using |
| 881 |
<structname>tenk1_four_unique1_idx</structname>, a multi-column index on the |
| 882 |
<structname>tenk1</structname> table's <structfield>four</structfield> and |
| 883 |
<structfield>unique1</structfield> columns. The scan performs 3 searches |
| 884 |
that each read a single index leaf page: |
| 885 |
<quote><literal>four = 1 AND unique1 = 42</literal></quote>, |
| 886 |
<quote><literal>four = 2 AND unique1 = 42</literal></quote>, and |
| 887 |
<quote><literal>four = 3 AND unique1 = 42</literal></quote>. This index |
| 888 |
is generally a good target for skip scan, since, as discussed in |
| 889 |
<xref linkend="indexes-multicolumn"/>, its leading column (the |
| 890 |
<structfield>four</structfield> column) contains only 4 distinct values, |
| 891 |
while its second/final column (the <structfield>unique1</structfield> |
| 892 |
column) contains many distinct values. |
| 893 |
</para> |
| 894 |
|
| 895 |
<para> |
| 896 |
Another type of extra information is the number of rows removed by a |
| 897 |
filter condition: |
| 898 |
|
| 899 |
<screen> |
| 900 |
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7; |
| 901 |
|
| 902 |
QUERY PLAN |
| 903 |
-------------------------------------------------------------------&zwsp;-------------------------------------- |
| 904 |
Seq Scan on tenk1 (cost=0.00..470.00 rows=7000 width=244) (actual time=0.030..1.995 rows=7000.00 loops=1) |
| 905 |
Filter: (ten < 7) |
| 906 |
Rows Removed by Filter: 3000 |
| 907 |
Buffers: shared hit=345 |
| 908 |
Planning Time: 0.102 ms |
| 909 |
Execution Time: 2.145 ms |
| 910 |
</screen> |
| 911 |
|
| 912 |
These counts can be particularly valuable for filter conditions applied at |
| 913 |
join nodes. The <quote>Rows Removed</quote> line only appears when at least |
| 914 |
one scanned row, or potential join pair in the case of a join node, |
| 915 |
is rejected by the filter condition. |
| 916 |
</para> |
| 917 |
|
| 918 |
<para> |
| 919 |
A case similar to filter conditions occurs with <quote>lossy</quote> |
| 920 |
index scans. For example, consider this search for polygons containing a |
| 921 |
specific point: |
| 922 |
|
| 923 |
<screen> |
| 924 |
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; |
| 925 |
|
| 926 |
QUERY PLAN |
| 927 |
-------------------------------------------------------------------&zwsp;----------------------------------- |
| 928 |
Seq Scan on polygon_tbl (cost=0.00..1.09 rows=1 width=85) (actual time=0.023..0.023 rows=0.00 loops=1) |
| 929 |
Filter: (f1 @> '((0.5,2))'::polygon) |
| 930 |
Rows Removed by Filter: 7 |
| 931 |
Buffers: shared hit=1 |
| 932 |
Planning Time: 0.039 ms |
| 933 |
Execution Time: 0.033 ms |
| 934 |
</screen> |
| 935 |
|
| 936 |
The planner thinks (quite correctly) that this sample table is too small |
| 937 |
to bother with an index scan, so we have a plain sequential scan in which |
| 938 |
all the rows got rejected by the filter condition. But if we force an |
| 939 |
index scan to be used, we see: |
| 940 |
|
| 941 |
<screen> |
| 942 |
SET enable_seqscan TO off; |
| 943 |
|
| 944 |
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)'; |
| 945 |
|
| 946 |
QUERY PLAN |
| 947 |
-------------------------------------------------------------------&zwsp;------------------------------------------------------- |
| 948 |
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=85) (actual time=0.074..0.074 rows=0.00 loops=1) |
| 949 |
Index Cond: (f1 @> '((0.5,2))'::polygon) |
| 950 |
Rows Removed by Index Recheck: 1 |
| 951 |
Index Searches: 1 |
| 952 |
Buffers: shared hit=1 |
| 953 |
Planning Time: 0.039 ms |
| 954 |
Execution Time: 0.098 ms |
| 955 |
</screen> |
| 956 |
|
| 957 |
Here we can see that the index returned one candidate row, which was |
| 958 |
then rejected by a recheck of the index condition. This happens because a |
| 959 |
GiST index is <quote>lossy</quote> for polygon containment tests: it actually |
| 960 |
returns the rows with polygons that overlap the target, and then we have |
| 961 |
to do the exact containment test on those rows. |
| 962 |
</para> |
| 963 |
|
| 964 |
<para> |
| 965 |
<command>EXPLAIN</command> has a <literal>BUFFERS</literal> option which |
| 966 |
provides additional detail about I/O operations performed during the |
| 967 |
planning and execution of the given query. The buffer numbers displayed |
| 968 |
show the count of the non-distinct buffers hit, read, dirtied, and written |
| 969 |
for the given node and all of its child nodes. The |
| 970 |
<literal>ANALYZE</literal> option implicitly enables the |
| 971 |
<literal>BUFFERS</literal> option. If this |
| 972 |
is undesired, <literal>BUFFERS</literal> may be explicitly disabled: |
| 973 |
|
| 974 |
<screen> |
| 975 |
EXPLAIN (ANALYZE, BUFFERS OFF) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000; |
| 976 |
|
| 977 |
QUERY PLAN |
| 978 |
-------------------------------------------------------------------&zwsp;-------------------------------------------------------------- |
| 979 |
Bitmap Heap Scan on tenk1 (cost=25.07..60.11 rows=10 width=244) (actual time=0.105..0.114 rows=10.00 loops=1) |
| 980 |
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000)) |
| 981 |
Heap Blocks: exact=10 |
| 982 |
-> BitmapAnd (cost=25.07..25.07 rows=10 width=0) (actual time=0.100..0.101 rows=0.00 loops=1) |
| 983 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.027..0.027 rows=100.00 loops=1) |
| 984 |
Index Cond: (unique1 < 100) |
| 985 |
Index Searches: 1 |
| 986 |
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.070..0.070 rows=999.00 loops=1) |
| 987 |
Index Cond: (unique2 > 9000) |
| 988 |
Index Searches: 1 |
| 989 |
Planning Time: 0.162 ms |
| 990 |
Execution Time: 0.143 ms |
| 991 |
</screen> |
| 992 |
</para> |
| 993 |
|
| 994 |
<para> |
| 995 |
Keep in mind that because <command>EXPLAIN ANALYZE</command> actually |
| 996 |
runs the query, any side-effects will happen as usual, even though |
| 997 |
whatever results the query might output are discarded in favor of |
| 998 |
printing the <command>EXPLAIN</command> data. If you want to analyze a |
| 999 |
data-modifying query without changing your tables, you can |
| 1000 |
roll the command back afterwards, for example: |
| 1001 |
|
| 1002 |
<screen> |
| 1003 |
BEGIN; |
| 1004 |
|
| 1005 |
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100; |
| 1006 |
|
| 1007 |
QUERY PLAN |
| 1008 |
-------------------------------------------------------------------&zwsp;------------------------------------------------------------- |
| 1009 |
Update on tenk1 (cost=5.06..225.23 rows=0 width=0) (actual time=1.634..1.635 rows=0.00 loops=1) |
| 1010 |
-> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=10) (actual time=0.065..0.141 rows=100.00 loops=1) |
| 1011 |
Recheck Cond: (unique1 < 100) |
| 1012 |
Heap Blocks: exact=90 |
| 1013 |
Buffers: shared hit=4 read=2 |
| 1014 |
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=100 width=0) (actual time=0.031..0.031 rows=100.00 loops=1) |
| 1015 |
Index Cond: (unique1 < 100) |
| 1016 |
Index Searches: 1 |
| 1017 |
Buffers: shared read=2 |
| 1018 |
Planning Time: 0.151 ms |
| 1019 |
Execution Time: 1.856 ms |
| 1020 |
|
| 1021 |
ROLLBACK; |
| 1022 |
</screen> |
| 1023 |
</para> |
| 1024 |
|
| 1025 |
<para> |
| 1026 |
As seen in this example, when the query is an <command>INSERT</command>, |
| 1027 |
<command>UPDATE</command>, <command>DELETE</command>, or |
| 1028 |
<command>MERGE</command> command, the actual work of |
| 1029 |
applying the table changes is done by a top-level Insert, Update, |
| 1030 |
Delete, or Merge plan node. The plan nodes underneath this node perform |
| 1031 |
the work of locating the old rows and/or computing the new data. |
| 1032 |
So above, we see the same sort of bitmap table scan we've seen already, |
| 1033 |
and its output is fed to an Update node that stores the updated rows. |
| 1034 |
It's worth noting that although the data-modifying node can take a |
| 1035 |
considerable amount of run time (here, it's consuming the lion's share |
| 1036 |
of the time), the planner does not currently add anything to the cost |
| 1037 |
estimates to account for that work. That's because the work to be done is |
| 1038 |
the same for every correct query plan, so it doesn't affect planning |
| 1039 |
decisions. |
| 1040 |
</para> |
| 1041 |
|
| 1042 |
<para> |
| 1043 |
When an <command>UPDATE</command>, <command>DELETE</command>, or |
| 1044 |
<command>MERGE</command> command affects a partitioned table or |
| 1045 |
inheritance hierarchy, the output might look like this: |
| 1046 |
|
| 1047 |
<screen> |
| 1048 |
EXPLAIN UPDATE gtest_parent SET f1 = CURRENT_DATE WHERE f2 = 101; |
| 1049 |
|
| 1050 |
QUERY PLAN |
| 1051 |
-------------------------------------------------------------------&zwsp;--------------------- |
| 1052 |
Update on gtest_parent (cost=0.00..3.06 rows=0 width=0) |
| 1053 |
Update on gtest_child gtest_parent_1 |
| 1054 |
Update on gtest_child2 gtest_parent_2 |
| 1055 |
Update on gtest_child3 gtest_parent_3 |
| 1056 |
-> Append (cost=0.00..3.06 rows=3 width=14) |
| 1057 |
-> Seq Scan on gtest_child gtest_parent_1 (cost=0.00..1.01 rows=1 width=14) |
| 1058 |
Filter: (f2 = 101) |
| 1059 |
-> Seq Scan on gtest_child2 gtest_parent_2 (cost=0.00..1.01 rows=1 width=14) |
| 1060 |
Filter: (f2 = 101) |
| 1061 |
-> Seq Scan on gtest_child3 gtest_parent_3 (cost=0.00..1.01 rows=1 width=14) |
| 1062 |
Filter: (f2 = 101) |
| 1063 |
</screen> |
| 1064 |
|
| 1065 |
In this example the Update node needs to consider three child tables, |
| 1066 |
but not the originally-mentioned partitioned table (since that never |
| 1067 |
stores any data). So there are three input |
| 1068 |
scanning subplans, one per table. For clarity, the Update node is |
| 1069 |
annotated to show the specific target tables that will be updated, in the |
| 1070 |
same order as the corresponding subplans. |
| 1071 |
</para> |
| 1072 |
|
| 1073 |
<para> |
| 1074 |
The <literal>Planning time</literal> shown by <command>EXPLAIN |
| 1075 |
ANALYZE</command> is the time it took to generate the query plan from the |
| 1076 |
parsed query and optimize it. It does not include parsing or rewriting. |
| 1077 |
</para> |
| 1078 |
|
| 1079 |
<para> |
| 1080 |
The <literal>Execution time</literal> shown by <command>EXPLAIN |
| 1081 |
ANALYZE</command> includes executor start-up and shut-down time, as well |
| 1082 |
as the time to run any triggers that are fired, but it does not include |
| 1083 |
parsing, rewriting, or planning time. |
| 1084 |
Time spent executing <literal>BEFORE</literal> triggers, if any, is included in |
| 1085 |
the time for the related Insert, Update, or Delete node; but time |
| 1086 |
spent executing <literal>AFTER</literal> triggers is not counted there because |
| 1087 |
<literal>AFTER</literal> triggers are fired after completion of the whole plan. |
| 1088 |
The total time spent in each trigger |
| 1089 |
(either <literal>BEFORE</literal> or <literal>AFTER</literal>) is also shown separately. |
| 1090 |
Note that deferred constraint triggers will not be executed |
| 1091 |
until end of transaction and are thus not considered at all by |
| 1092 |
<command>EXPLAIN ANALYZE</command>. |
| 1093 |
</para> |
| 1094 |
|
| 1095 |
<para> |
| 1096 |
The time shown for the top-level node does not include any time needed |
| 1097 |
to convert the query's output data into displayable form or to send it |
| 1098 |
to the client. While <command>EXPLAIN ANALYZE</command> will never |
| 1099 |
send the data to the client, it can be told to convert the query's |
| 1100 |
output data to displayable form and measure the time needed for that, |
| 1101 |
by specifying the <literal>SERIALIZE</literal> option. That time will |
| 1102 |
be shown separately, and it's also included in the |
| 1103 |
total <literal>Execution time</literal>. |
| 1104 |
</para> |
| 1105 |
|
| 1106 |
</sect2> |
| 1107 |
|
| 1108 |
<sect2 id="using-explain-caveats"> |
| 1109 |
<title>Caveats</title> |
| 1110 |
|
| 1111 |
<para> |
| 1112 |
There are two significant ways in which run times measured by |
| 1113 |
<command>EXPLAIN ANALYZE</command> can deviate from normal execution of |
| 1114 |
the same query. First, since no output rows are delivered to the client, |
| 1115 |
network transmission costs are not included. I/O conversion costs are |
| 1116 |
not included either unless <literal>SERIALIZE</literal> is specified. |
| 1117 |
Second, the measurement overhead added by <command>EXPLAIN |
| 1118 |
ANALYZE</command> can be significant, especially on machines with slow |
| 1119 |
<function>gettimeofday()</function> operating-system calls. You can use the |
| 1120 |
<xref linkend="pgtesttiming"/> tool to measure the overhead of timing |
| 1121 |
on your system. |
| 1122 |
</para> |
| 1123 |
|
| 1124 |
<para> |
| 1125 |
<command>EXPLAIN</command> results should not be extrapolated to situations |
| 1126 |
much different from the one you are actually testing; for example, |
| 1127 |
results on a toy-sized table cannot be assumed to apply to large tables. |
| 1128 |
The planner's cost estimates are not linear and so it might choose |
| 1129 |
a different plan for a larger or smaller table. An extreme example |
| 1130 |
is that on a table that only occupies one disk page, you'll nearly |
| 1131 |
always get a sequential scan plan whether indexes are available or not. |
| 1132 |
The planner realizes that it's going to take one disk page read to |
| 1133 |
process the table in any case, so there's no value in expending additional |
| 1134 |
page reads to look at an index. (We saw this happening in the |
| 1135 |
<literal>polygon_tbl</literal> example above.) |
| 1136 |
</para> |
| 1137 |
|
| 1138 |
<para> |
| 1139 |
There are cases in which the actual and estimated values won't match up |
| 1140 |
well, but nothing is really wrong. One such case occurs when |
| 1141 |
plan node execution is stopped short by a <literal>LIMIT</literal> or similar |
| 1142 |
effect. For example, in the <literal>LIMIT</literal> query we used before, |
| 1143 |
|
| 1144 |
<screen> |
| 1145 |
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; |
| 1146 |
|
| 1147 |
QUERY PLAN |
| 1148 |
-------------------------------------------------------------------&zwsp;------------------------------------------------------------ |
| 1149 |
Limit (cost=0.29..14.33 rows=2 width=244) (actual time=0.051..0.071 rows=2.00 loops=1) |
| 1150 |
Buffers: shared hit=16 |
| 1151 |
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..70.50 rows=10 width=244) (actual time=0.051..0.070 rows=2.00 loops=1) |
| 1152 |
Index Cond: (unique2 > 9000) |
| 1153 |
Filter: (unique1 < 100) |
| 1154 |
Rows Removed by Filter: 287 |
| 1155 |
Index Searches: 1 |
| 1156 |
Buffers: shared hit=16 |
| 1157 |
Planning Time: 0.077 ms |
| 1158 |
Execution Time: 0.086 ms |
| 1159 |
</screen> |
| 1160 |
|
| 1161 |
the estimated cost and row count for the Index Scan node are shown as |
| 1162 |
though it were run to completion. But in reality the Limit node stopped |
| 1163 |
requesting rows after it got two, so the actual row count is only 2 and |
| 1164 |
the run time is less than the cost estimate would suggest. This is not |
| 1165 |
an estimation error, only a discrepancy in the way the estimates and true |
| 1166 |
values are displayed. |
| 1167 |
</para> |
| 1168 |
|
| 1169 |
<para> |
| 1170 |
Merge joins also have measurement artifacts that can confuse the unwary. |
| 1171 |
A merge join will stop reading one input if it's exhausted the other input |
| 1172 |
and the next key value in the one input is greater than the last key value |
| 1173 |
of the other input; in such a case there can be no more matches and so no |
| 1174 |
need to scan the rest of the first input. This results in not reading all |
| 1175 |
of one child, with results like those mentioned for <literal>LIMIT</literal>. |
| 1176 |
Also, if the outer (first) child contains rows with duplicate key values, |
| 1177 |
the inner (second) child is backed up and rescanned for the portion of its |
| 1178 |
rows matching that key value. <command>EXPLAIN ANALYZE</command> counts these |
| 1179 |
repeated emissions of the same inner rows as if they were real additional |
| 1180 |
rows. When there are many outer duplicates, the reported actual row count |
| 1181 |
for the inner child plan node can be significantly larger than the number |
| 1182 |
of rows that are actually in the inner relation. |
| 1183 |
</para> |
| 1184 |
|
| 1185 |
<para> |
| 1186 |
BitmapAnd and BitmapOr nodes always report their actual row counts as zero, |
| 1187 |
due to implementation limitations. |
| 1188 |
</para> |
| 1189 |
|
| 1190 |
<para> |
| 1191 |
Normally, <command>EXPLAIN</command> will display every plan node |
| 1192 |
created by the planner. However, there are cases where the executor |
| 1193 |
can determine that certain nodes need not be executed because they |
| 1194 |
cannot produce any rows, based on parameter values that were not |
| 1195 |
available at planning time. (Currently this can only happen for child |
| 1196 |
nodes of an Append or MergeAppend node that is scanning a partitioned |
| 1197 |
table.) When this happens, those plan nodes are omitted from |
| 1198 |
the <command>EXPLAIN</command> output and a <literal>Subplans |
| 1199 |
Removed: <replaceable>N</replaceable></literal> annotation appears |
| 1200 |
instead. |
| 1201 |
</para> |
| 1202 |
</sect2> |
| 1203 |
|
| 1204 |
</sect1> |
| 1205 |
|
| 1206 |
<sect1 id="planner-stats"> |
| 1207 |
<title>Statistics Used by the Planner</title> |
| 1208 |
|
| 1209 |
<indexterm zone="planner-stats"> |
| 1210 |
<primary>statistics</primary> |
| 1211 |
<secondary>of the planner</secondary> |
| 1212 |
</indexterm> |
| 1213 |
|
| 1214 |
<sect2 id="planner-stats-single-column"> |
| 1215 |
<title>Single-Column Statistics</title> |
| 1216 |
<para> |
| 1217 |
As we saw in the previous section, the query planner needs to estimate |
| 1218 |
the number of rows retrieved by a query in order to make good choices |
| 1219 |
of query plans. This section provides a quick look at the statistics |
| 1220 |
that the system uses for these estimates. |
| 1221 |
</para> |
| 1222 |
|
| 1223 |
<para> |
| 1224 |
One component of the statistics is the total number of entries in |
| 1225 |
each table and index, as well as the number of disk blocks occupied |
| 1226 |
by each table and index. This information is kept in the table |
| 1227 |
<link linkend="catalog-pg-class"><structname>pg_class</structname></link>, |
| 1228 |
in the columns <structfield>reltuples</structfield> and |
| 1229 |
<structfield>relpages</structfield>. We can look at it with |
| 1230 |
queries similar to this one: |
| 1231 |
|
| 1232 |
<screen> |
| 1233 |
SELECT relname, relkind, reltuples, relpages |
| 1234 |
FROM pg_class |
| 1235 |
WHERE relname LIKE 'tenk1%'; |
| 1236 |
|
| 1237 |
relname | relkind | reltuples | relpages |
| 1238 |
----------------------+---------+-----------+---------- |
| 1239 |
tenk1 | r | 10000 | 345 |
| 1240 |
tenk1_hundred | i | 10000 | 11 |
| 1241 |
tenk1_thous_tenthous | i | 10000 | 30 |
| 1242 |
tenk1_unique1 | i | 10000 | 30 |
| 1243 |
tenk1_unique2 | i | 10000 | 30 |
| 1244 |
(5 rows) |
| 1245 |
</screen> |
| 1246 |
|
| 1247 |
Here we can see that <structname>tenk1</structname> contains 10000 |
| 1248 |
rows, as do its indexes, but the indexes are (unsurprisingly) much |
| 1249 |
smaller than the table. |
| 1250 |
</para> |
| 1251 |
|
| 1252 |
<para> |
| 1253 |
For efficiency reasons, <structfield>reltuples</structfield> |
| 1254 |
and <structfield>relpages</structfield> are not updated on-the-fly, |
| 1255 |
and so they usually contain somewhat out-of-date values. |
| 1256 |
They are updated by <command>VACUUM</command>, <command>ANALYZE</command>, and a |
| 1257 |
few DDL commands such as <command>CREATE INDEX</command>. A <command>VACUUM</command> |
| 1258 |
or <command>ANALYZE</command> operation that does not scan the entire table |
| 1259 |
(which is commonly the case) will incrementally update the |
| 1260 |
<structfield>reltuples</structfield> count on the basis of the part |
| 1261 |
of the table it did scan, resulting in an approximate value. |
| 1262 |
In any case, the planner |
| 1263 |
will scale the values it finds in <structname>pg_class</structname> |
| 1264 |
to match the current physical table size, thus obtaining a closer |
| 1265 |
approximation. |
| 1266 |
</para> |
| 1267 |
|
| 1268 |
<indexterm> |
| 1269 |
<primary>pg_statistic</primary> |
| 1270 |
</indexterm> |
| 1271 |
|
| 1272 |
<para> |
| 1273 |
Most queries retrieve only a fraction of the rows in a table, due |
| 1274 |
to <literal>WHERE</literal> clauses that restrict the rows to be |
| 1275 |
examined. The planner thus needs to make an estimate of the |
| 1276 |
<firstterm>selectivity</firstterm> of <literal>WHERE</literal> clauses, that is, |
| 1277 |
the fraction of rows that match each condition in the |
| 1278 |
<literal>WHERE</literal> clause. The information used for this task is |
| 1279 |
stored in the |
| 1280 |
<link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link> |
| 1281 |
system catalog. Entries in <structname>pg_statistic</structname> |
| 1282 |
are updated by the <command>ANALYZE</command> and <command>VACUUM |
| 1283 |
ANALYZE</command> commands, and are always approximate even when freshly |
| 1284 |
updated. |
| 1285 |
</para> |
| 1286 |
|
| 1287 |
<indexterm> |
| 1288 |
<primary>pg_stats</primary> |
| 1289 |
</indexterm> |
| 1290 |
|
| 1291 |
<para> |
| 1292 |
Rather than look at <structname>pg_statistic</structname> directly, |
| 1293 |
it's better to look at its view |
| 1294 |
<link linkend="view-pg-stats"><structname>pg_stats</structname></link> |
| 1295 |
when examining the statistics manually. <structname>pg_stats</structname> |
| 1296 |
is designed to be more easily readable. Furthermore, |
| 1297 |
<structname>pg_stats</structname> is readable by all, whereas |
| 1298 |
<structname>pg_statistic</structname> is only readable by a superuser. |
| 1299 |
(This prevents unprivileged users from learning something about |
| 1300 |
the contents of other people's tables from the statistics. The |
| 1301 |
<structname>pg_stats</structname> view is restricted to show only |
| 1302 |
rows about tables that the current user can read.) |
| 1303 |
For example, we might do: |
| 1304 |
|
| 1305 |
<screen> |
| 1306 |
SELECT attname, inherited, n_distinct, |
| 1307 |
array_to_string(most_common_vals, E'\n') as most_common_vals |
| 1308 |
FROM pg_stats |
| 1309 |
WHERE tablename = 'road'; |
| 1310 |
|
| 1311 |
attname | inherited | n_distinct | most_common_vals |
| 1312 |
---------+-----------+------------+------------------------------------ |
| 1313 |
name | f | -0.5681108 | I- 580 Ramp+ |
| 1314 |
| | | I- 880 Ramp+ |
| 1315 |
| | | Sp Railroad + |
| 1316 |
| | | I- 580 + |
| 1317 |
| | | I- 680 Ramp+ |
| 1318 |
| | | I- 80 Ramp+ |
| 1319 |
| | | 14th St + |
| 1320 |
| | | I- 880 + |
| 1321 |
| | | Mac Arthur Blvd+ |
| 1322 |
| | | Mission Blvd+ |
| 1323 |
... |
| 1324 |
name | t | -0.5125 | I- 580 Ramp+ |
| 1325 |
| | | I- 880 Ramp+ |
| 1326 |
| | | I- 580 + |
| 1327 |
| | | I- 680 Ramp+ |
| 1328 |
| | | I- 80 Ramp+ |
| 1329 |
| | | Sp Railroad + |
| 1330 |
| | | I- 880 + |
| 1331 |
| | | State Hwy 13 Ramp+ |
| 1332 |
| | | I- 80 + |
| 1333 |
| | | State Hwy 24 Ramp+ |
| 1334 |
... |
| 1335 |
thepath | f | 0 | |
| 1336 |
thepath | t | 0 | |
| 1337 |
(4 rows) |
| 1338 |
</screen> |
| 1339 |
|
| 1340 |
Note that two rows are displayed for the same column, one corresponding |
| 1341 |
to the complete inheritance hierarchy starting at the |
| 1342 |
<literal>road</literal> table (<literal>inherited</literal>=<literal>t</literal>), |
| 1343 |
and another one including only the <literal>road</literal> table itself |
| 1344 |
(<literal>inherited</literal>=<literal>f</literal>). |
| 1345 |
(For brevity, we have only shown the first ten most-common values for |
| 1346 |
the <literal>name</literal> column.) |
| 1347 |
</para> |
| 1348 |
|
| 1349 |
<para> |
| 1350 |
The amount of information stored in <structname>pg_statistic</structname> |
| 1351 |
by <command>ANALYZE</command>, in particular the maximum number of entries in the |
| 1352 |
<structfield>most_common_vals</structfield> and <structfield>histogram_bounds</structfield> |
| 1353 |
arrays for each column, can be set on a |
| 1354 |
column-by-column basis using the <command>ALTER TABLE SET STATISTICS</command> |
| 1355 |
command, or globally by setting the |
| 1356 |
<xref linkend="guc-default-statistics-target"/> configuration variable. |
| 1357 |
The default limit is presently 100 entries. Raising the limit |
| 1358 |
might allow more accurate planner estimates to be made, particularly for |
| 1359 |
columns with irregular data distributions, at the price of consuming |
| 1360 |
more space in <structname>pg_statistic</structname> and slightly more |
| 1361 |
time to compute the estimates. Conversely, a lower limit might be |
| 1362 |
sufficient for columns with simple data distributions. |
| 1363 |
</para> |
| 1364 |
|
| 1365 |
<para> |
| 1366 |
Further details about the planner's use of statistics can be found in |
| 1367 |
<xref linkend="planner-stats-details"/>. |
| 1368 |
</para> |
| 1369 |
</sect2> |
| 1370 |
|
| 1371 |
<sect2 id="planner-stats-extended"> |
| 1372 |
<title>Extended Statistics</title> |
| 1373 |
|
| 1374 |
<indexterm zone="planner-stats-extended"> |
| 1375 |
<primary>statistics</primary> |
| 1376 |
<secondary>of the planner</secondary> |
| 1377 |
</indexterm> |
| 1378 |
|
| 1379 |
<indexterm> |
| 1380 |
<primary>correlation</primary> |
| 1381 |
<secondary>in the query planner</secondary> |
| 1382 |
</indexterm> |
| 1383 |
|
| 1384 |
<indexterm> |
| 1385 |
<primary>pg_statistic_ext</primary> |
| 1386 |
</indexterm> |
| 1387 |
|
| 1388 |
<indexterm> |
| 1389 |
<primary>pg_statistic_ext_data</primary> |
| 1390 |
</indexterm> |
| 1391 |
|
| 1392 |
<para> |
| 1393 |
It is common to see slow queries running bad execution plans because |
| 1394 |
multiple columns used in the query clauses are correlated. |
| 1395 |
The planner normally assumes that multiple conditions |
| 1396 |
are independent of each other, |
| 1397 |
an assumption that does not hold when column values are correlated. |
| 1398 |
Regular statistics, because of their per-individual-column nature, |
| 1399 |
cannot capture any knowledge about cross-column correlation. |
| 1400 |
However, <productname>PostgreSQL</productname> has the ability to compute |
| 1401 |
<firstterm>multivariate statistics</firstterm>, which can capture |
| 1402 |
such information. |
| 1403 |
</para> |
| 1404 |
|
| 1405 |
<para> |
| 1406 |
Because the number of possible column combinations is very large, |
| 1407 |
it's impractical to compute multivariate statistics automatically. |
| 1408 |
Instead, <firstterm>extended statistics objects</firstterm>, more often |
| 1409 |
called just <firstterm>statistics objects</firstterm>, can be created to instruct |
| 1410 |
the server to obtain statistics across interesting sets of columns. |
| 1411 |
</para> |
| 1412 |
|
| 1413 |
<para> |
| 1414 |
Statistics objects are created using the |
| 1415 |
<link linkend="sql-createstatistics"><command>CREATE STATISTICS</command></link> command. |
| 1416 |
Creation of such an object merely creates a catalog entry expressing |
| 1417 |
interest in the statistics. Actual data collection is performed |
| 1418 |
by <command>ANALYZE</command> (either a manual command, or background |
| 1419 |
auto-analyze). The collected values can be examined in the |
| 1420 |
<link linkend="catalog-pg-statistic-ext-data"><structname>pg_statistic_ext_data</structname></link> |
| 1421 |
catalog. |
| 1422 |
</para> |
| 1423 |
|
| 1424 |
<para> |
| 1425 |
<command>ANALYZE</command> computes extended statistics based on the same |
| 1426 |
sample of table rows that it takes for computing regular single-column |
| 1427 |
statistics. Since the sample size is increased by increasing the |
| 1428 |
statistics target for the table or any of its columns (as described in |
| 1429 |
the previous section), a larger statistics target will normally result in |
| 1430 |
more accurate extended statistics, as well as more time spent calculating |
| 1431 |
them. |
| 1432 |
</para> |
| 1433 |
|
| 1434 |
<para> |
| 1435 |
The following subsections describe the kinds of extended statistics |
| 1436 |
that are currently supported. |
| 1437 |
</para> |
| 1438 |
|
| 1439 |
<sect3 id="planner-stats-extended-functional-deps"> |
| 1440 |
<title>Functional Dependencies</title> |
| 1441 |
|
| 1442 |
<para> |
| 1443 |
The simplest kind of extended statistics tracks <firstterm>functional |
| 1444 |
dependencies</firstterm>, a concept used in definitions of database normal forms. |
| 1445 |
We say that column <structfield>b</structfield> is functionally dependent on |
| 1446 |
column <structfield>a</structfield> if knowledge of the value of |
| 1447 |
<structfield>a</structfield> is sufficient to determine the value |
| 1448 |
of <structfield>b</structfield>, that is there are no two rows having the same value |
| 1449 |
of <structfield>a</structfield> but different values of <structfield>b</structfield>. |
| 1450 |
In a fully normalized database, functional dependencies should exist |
| 1451 |
only on primary keys and superkeys. However, in practice many data sets |
| 1452 |
are not fully normalized for various reasons; intentional |
| 1453 |
denormalization for performance reasons is a common example. |
| 1454 |
Even in a fully normalized database, there may be partial correlation |
| 1455 |
between some columns, which can be expressed as partial functional |
| 1456 |
dependency. |
| 1457 |
</para> |
| 1458 |
|
| 1459 |
<para> |
| 1460 |
The existence of functional dependencies directly affects the accuracy |
| 1461 |
of estimates in certain queries. If a query contains conditions on |
| 1462 |
both the independent and the dependent column(s), the |
| 1463 |
conditions on the dependent columns do not further reduce the result |
| 1464 |
size; but without knowledge of the functional dependency, the query |
| 1465 |
planner will assume that the conditions are independent, resulting |
| 1466 |
in underestimating the result size. |
| 1467 |
</para> |
| 1468 |
|
| 1469 |
<para> |
| 1470 |
To inform the planner about functional dependencies, <command>ANALYZE</command> |
| 1471 |
can collect measurements of cross-column dependency. Assessing the |
| 1472 |
degree of dependency between all sets of columns would be prohibitively |
| 1473 |
expensive, so data collection is limited to those groups of columns |
| 1474 |
appearing together in a statistics object defined with |
| 1475 |
the <literal>dependencies</literal> option. It is advisable to create |
| 1476 |
<literal>dependencies</literal> statistics only for column groups that are |
| 1477 |
strongly correlated, to avoid unnecessary overhead in both |
| 1478 |
<command>ANALYZE</command> and later query planning. |
| 1479 |
</para> |
| 1480 |
|
| 1481 |
<para> |
| 1482 |
Here is an example of collecting functional-dependency statistics: |
| 1483 |
<programlisting> |
| 1484 |
CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes; |
| 1485 |
|
| 1486 |
ANALYZE zipcodes; |
| 1487 |
|
| 1488 |
SELECT stxname, stxkeys, stxddependencies |
| 1489 |
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) |
| 1490 |
WHERE stxname = 'stts'; |
| 1491 |
stxname | stxkeys | stxddependencies |
| 1492 |
---------+---------+------------------------------------------ |
| 1493 |
stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130} |
| 1494 |
(1 row) |
| 1495 |
</programlisting> |
| 1496 |
Here it can be seen that column 1 (zip code) fully determines column |
| 1497 |
5 (city) so the coefficient is 1.0, while city only determines zip code |
| 1498 |
about 42% of the time, meaning that there are many cities (58%) that are |
| 1499 |
represented by more than a single ZIP code. |
| 1500 |
</para> |
| 1501 |
|
| 1502 |
<para> |
| 1503 |
When computing the selectivity for a query involving functionally |
| 1504 |
dependent columns, the planner adjusts the per-condition selectivity |
| 1505 |
estimates using the dependency coefficients so as not to produce |
| 1506 |
an underestimate. |
| 1507 |
</para> |
| 1508 |
|
| 1509 |
<sect4 id="planner-stats-extended-functional-deps-limits"> |
| 1510 |
<title>Limitations of Functional Dependencies</title> |
| 1511 |
|
| 1512 |
<para> |
| 1513 |
Functional dependencies are currently only applied when considering |
| 1514 |
simple equality conditions that compare columns to constant values, |
| 1515 |
and <literal>IN</literal> clauses with constant values. |
| 1516 |
They are not used to improve estimates for equality conditions |
| 1517 |
comparing two columns or comparing a column to an expression, nor for |
| 1518 |
range clauses, <literal>LIKE</literal> or any other type of condition. |
| 1519 |
</para> |
| 1520 |
|
| 1521 |
<para> |
| 1522 |
When estimating with functional dependencies, the planner assumes that |
| 1523 |
conditions on the involved columns are compatible and hence redundant. |
| 1524 |
If they are incompatible, the correct estimate would be zero rows, but |
| 1525 |
that possibility is not considered. For example, given a query like |
| 1526 |
<programlisting> |
| 1527 |
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105'; |
| 1528 |
</programlisting> |
| 1529 |
the planner will disregard the <structfield>city</structfield> clause as not |
| 1530 |
changing the selectivity, which is correct. However, it will make |
| 1531 |
the same assumption about |
| 1532 |
<programlisting> |
| 1533 |
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210'; |
| 1534 |
</programlisting> |
| 1535 |
even though there will really be zero rows satisfying this query. |
| 1536 |
Functional dependency statistics do not provide enough information |
| 1537 |
to conclude that, however. |
| 1538 |
</para> |
| 1539 |
|
| 1540 |
<para> |
| 1541 |
In many practical situations, this assumption is usually satisfied; |
| 1542 |
for example, there might be a GUI in the application that only allows |
| 1543 |
selecting compatible city and ZIP code values to use in a query. |
| 1544 |
But if that's not the case, functional dependencies may not be a viable |
| 1545 |
option. |
| 1546 |
</para> |
| 1547 |
</sect4> |
| 1548 |
</sect3> |
| 1549 |
|
| 1550 |
<sect3 id="planner-stats-extended-n-distinct-counts"> |
| 1551 |
<title>Multivariate N-Distinct Counts</title> |
| 1552 |
|
| 1553 |
<para> |
| 1554 |
Single-column statistics store the number of distinct values in each |
| 1555 |
column. Estimates of the number of distinct values when combining more |
| 1556 |
than one column (for example, for <literal>GROUP BY a, b</literal>) are |
| 1557 |
frequently wrong when the planner only has single-column statistical |
| 1558 |
data, causing it to select bad plans. |
| 1559 |
</para> |
| 1560 |
|
| 1561 |
<para> |
| 1562 |
To improve such estimates, <command>ANALYZE</command> can collect n-distinct |
| 1563 |
statistics for groups of columns. As before, it's impractical to do |
| 1564 |
this for every possible column grouping, so data is collected only for |
| 1565 |
those groups of columns appearing together in a statistics object |
| 1566 |
defined with the <literal>ndistinct</literal> option. Data will be collected |
| 1567 |
for each possible combination of two or more columns from the set of |
| 1568 |
listed columns. |
| 1569 |
</para> |
| 1570 |
|
| 1571 |
<para> |
| 1572 |
Continuing the previous example, the n-distinct counts in a |
| 1573 |
table of ZIP codes might look like the following: |
| 1574 |
<programlisting> |
| 1575 |
CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes; |
| 1576 |
|
| 1577 |
ANALYZE zipcodes; |
| 1578 |
|
| 1579 |
SELECT stxkeys AS k, stxdndistinct AS nd |
| 1580 |
FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid) |
| 1581 |
WHERE stxname = 'stts2'; |
| 1582 |
-[ RECORD 1 ]------------------------------------------------------&zwsp;-- |
| 1583 |
k | 1 2 5 |
| 1584 |
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178} |
| 1585 |
(1 row) |
| 1586 |
</programlisting> |
| 1587 |
This indicates that there are three combinations of columns that |
| 1588 |
have 33178 distinct values: ZIP code and state; ZIP code and city; |
| 1589 |
and ZIP code, city and state (the fact that they are all equal is |
| 1590 |
expected given that ZIP code alone is unique in this table). On the |
| 1591 |
other hand, the combination of city and state has only 27435 distinct |
| 1592 |
values. |
| 1593 |
</para> |
| 1594 |
|
| 1595 |
<para> |
| 1596 |
It's advisable to create <literal>ndistinct</literal> statistics objects only |
| 1597 |
on combinations of columns that are actually used for grouping, and |
| 1598 |
for which misestimation of the number of groups is resulting in bad |
| 1599 |
plans. Otherwise, the <command>ANALYZE</command> cycles are just wasted. |
| 1600 |
</para> |
| 1601 |
</sect3> |
| 1602 |
|
| 1603 |
<sect3 id="planner-stats-extended-mcv-lists"> |
| 1604 |
<title>Multivariate MCV Lists</title> |
| 1605 |
|
| 1606 |
<para> |
| 1607 |
Another type of statistic stored for each column are most-common value |
| 1608 |
lists. This allows very accurate estimates for individual columns, but |
| 1609 |
may result in significant misestimates for queries with conditions on |
| 1610 |
multiple columns. |
| 1611 |
</para> |
| 1612 |
|
| 1613 |
<para> |
| 1614 |
To improve such estimates, <command>ANALYZE</command> can collect MCV |
| 1615 |
lists on combinations of columns. Similarly to functional dependencies |
| 1616 |
and n-distinct coefficients, it's impractical to do this for every |
| 1617 |
possible column grouping. Even more so in this case, as the MCV list |
| 1618 |
(unlike functional dependencies and n-distinct coefficients) does store |
| 1619 |
the common column values. So data is collected only for those groups |
| 1620 |
of columns appearing together in a statistics object defined with the |
| 1621 |
<literal>mcv</literal> option. |
| 1622 |
</para> |
| 1623 |
|
| 1624 |
<para> |
| 1625 |
Continuing the previous example, the MCV list for a table of ZIP codes |
| 1626 |
might look like the following (unlike for simpler types of statistics, |
| 1627 |
a function is required for inspection of MCV contents): |
| 1628 |
|
| 1629 |
<programlisting> |
| 1630 |
CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes; |
| 1631 |
|
| 1632 |
ANALYZE zipcodes; |
| 1633 |
|
| 1634 |
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), |
| 1635 |
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3'; |
| 1636 |
|
| 1637 |
index | values | nulls | frequency | base_frequency |
| 1638 |
-------+------------------------+-------+-----------+---------------- |
| 1639 |
0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05 |
| 1640 |
1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05 |
| 1641 |
2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133 |
| 1642 |
3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113 |
| 1643 |
4 | {New York, NY} | {f,f} | 0.001967 | 0.000114 |
| 1644 |
5 | {Atlanta, GA} | {f,f} | 0.001633 | 3.3e-05 |
| 1645 |
6 | {Sacramento, CA} | {f,f} | 0.001433 | 7.8e-05 |
| 1646 |
7 | {Miami, FL} | {f,f} | 0.0014 | 6e-05 |
| 1647 |
8 | {Dallas, TX} | {f,f} | 0.001367 | 8.8e-05 |
| 1648 |
9 | {Chicago, IL} | {f,f} | 0.001333 | 5.1e-05 |
| 1649 |
... |
| 1650 |
(99 rows) |
| 1651 |
</programlisting> |
| 1652 |
This indicates that the most common combination of city and state is |
| 1653 |
Washington in DC, with actual frequency (in the sample) about 0.35%. |
| 1654 |
The base frequency of the combination (as computed from the simple |
| 1655 |
per-column frequencies) is only 0.0027%, resulting in two orders of |
| 1656 |
magnitude under-estimates. |
| 1657 |
</para> |
| 1658 |
|
| 1659 |
<para> |
| 1660 |
It's advisable to create <acronym>MCV</acronym> statistics objects only |
| 1661 |
on combinations of columns that are actually used in conditions together, |
| 1662 |
and for which misestimation of the number of groups is resulting in bad |
| 1663 |
plans. Otherwise, the <command>ANALYZE</command> and planning cycles |
| 1664 |
are just wasted. |
| 1665 |
</para> |
| 1666 |
</sect3> |
| 1667 |
|
| 1668 |
</sect2> |
| 1669 |
</sect1> |
| 1670 |
|
| 1671 |
<sect1 id="explicit-joins"> |
| 1672 |
<title>Controlling the Planner with Explicit <literal>JOIN</literal> Clauses</title> |
| 1673 |
|
| 1674 |
<indexterm zone="explicit-joins"> |
| 1675 |
<primary>join</primary> |
| 1676 |
<secondary>controlling the order</secondary> |
| 1677 |
</indexterm> |
| 1678 |
|
| 1679 |
<para> |
| 1680 |
It is possible |
| 1681 |
to control the query planner to some extent by using the explicit <literal>JOIN</literal> |
| 1682 |
syntax. To see why this matters, we first need some background. |
| 1683 |
</para> |
| 1684 |
|
| 1685 |
<para> |
| 1686 |
In a simple join query, such as: |
| 1687 |
<programlisting> |
| 1688 |
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; |
| 1689 |
</programlisting> |
| 1690 |
the planner is free to join the given tables in any order. For |
| 1691 |
example, it could generate a query plan that joins A to B, using |
| 1692 |
the <literal>WHERE</literal> condition <literal>a.id = b.id</literal>, and then |
| 1693 |
joins C to this joined table, using the other <literal>WHERE</literal> |
| 1694 |
condition. Or it could join B to C and then join A to that result. |
| 1695 |
Or it could join A to C and then join them with B — but that |
| 1696 |
would be inefficient, since the full Cartesian product of A and C |
| 1697 |
would have to be formed, there being no applicable condition in the |
| 1698 |
<literal>WHERE</literal> clause to allow optimization of the join. (All |
| 1699 |
joins in the <productname>PostgreSQL</productname> executor happen |
| 1700 |
between two input tables, so it's necessary to build up the result |
| 1701 |
in one or another of these fashions.) The important point is that |
| 1702 |
these different join possibilities give semantically equivalent |
| 1703 |
results but might have hugely different execution costs. Therefore, |
| 1704 |
the planner will explore all of them to try to find the most |
| 1705 |
efficient query plan. |
| 1706 |
</para> |
| 1707 |
|
| 1708 |
<para> |
| 1709 |
When a query only involves two or three tables, there aren't many join |
| 1710 |
orders to worry about. But the number of possible join orders grows |
| 1711 |
exponentially as the number of tables expands. Beyond ten or so input |
| 1712 |
tables it's no longer practical to do an exhaustive search of all the |
| 1713 |
possibilities, and even for six or seven tables planning might take an |
| 1714 |
annoyingly long time. When there are too many input tables, the |
| 1715 |
<productname>PostgreSQL</productname> planner will switch from exhaustive |
| 1716 |
search to a <firstterm>genetic</firstterm> probabilistic search |
| 1717 |
through a limited number of possibilities. (The switch-over threshold is |
| 1718 |
set by the <xref linkend="guc-geqo-threshold"/> run-time |
| 1719 |
parameter.) |
| 1720 |
The genetic search takes less time, but it won't |
| 1721 |
necessarily find the best possible plan. |
| 1722 |
</para> |
| 1723 |
|
| 1724 |
<para> |
| 1725 |
When the query involves outer joins, the planner has less freedom |
| 1726 |
than it does for plain (inner) joins. For example, consider: |
| 1727 |
<programlisting> |
| 1728 |
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); |
| 1729 |
</programlisting> |
| 1730 |
Although this query's restrictions are superficially similar to the |
| 1731 |
previous example, the semantics are different because a row must be |
| 1732 |
emitted for each row of A that has no matching row in the join of B and C. |
| 1733 |
Therefore the planner has no choice of join order here: it must join |
| 1734 |
B to C and then join A to that result. Accordingly, this query takes |
| 1735 |
less time to plan than the previous query. In other cases, the planner |
| 1736 |
might be able to determine that more than one join order is safe. |
| 1737 |
For example, given: |
| 1738 |
<programlisting> |
| 1739 |
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id); |
| 1740 |
</programlisting> |
| 1741 |
it is valid to join A to either B or C first. Currently, only |
| 1742 |
<literal>FULL JOIN</literal> completely constrains the join order. Most |
| 1743 |
practical cases involving <literal>LEFT JOIN</literal> or <literal>RIGHT JOIN</literal> |
| 1744 |
can be rearranged to some extent. |
| 1745 |
</para> |
| 1746 |
|
| 1747 |
<para> |
| 1748 |
Explicit inner join syntax (<literal>INNER JOIN</literal>, <literal>CROSS |
| 1749 |
JOIN</literal>, or unadorned <literal>JOIN</literal>) is semantically the same as |
| 1750 |
listing the input relations in <literal>FROM</literal>, so it does not |
| 1751 |
constrain the join order. |
| 1752 |
</para> |
| 1753 |
|
| 1754 |
<para> |
| 1755 |
Even though most kinds of <literal>JOIN</literal> don't completely constrain |
| 1756 |
the join order, it is possible to instruct the |
| 1757 |
<productname>PostgreSQL</productname> query planner to treat all |
| 1758 |
<literal>JOIN</literal> clauses as constraining the join order anyway. |
| 1759 |
For example, these three queries are logically equivalent: |
| 1760 |
<programlisting> |
| 1761 |
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id; |
| 1762 |
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id; |
| 1763 |
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id); |
| 1764 |
</programlisting> |
| 1765 |
But if we tell the planner to honor the <literal>JOIN</literal> order, |
| 1766 |
the second and third take less time to plan than the first. This effect |
| 1767 |
is not worth worrying about for only three tables, but it can be a |
| 1768 |
lifesaver with many tables. |
| 1769 |
</para> |
| 1770 |
|
| 1771 |
<para> |
| 1772 |
To force the planner to follow the join order laid out by explicit |
| 1773 |
<literal>JOIN</literal>s, |
| 1774 |
set the <xref linkend="guc-join-collapse-limit"/> run-time parameter to 1. |
| 1775 |
(Other possible values are discussed below.) |
| 1776 |
</para> |
| 1777 |
|
| 1778 |
<para> |
| 1779 |
You do not need to constrain the join order completely in order to |
| 1780 |
cut search time, because it's OK to use <literal>JOIN</literal> operators |
| 1781 |
within items of a plain <literal>FROM</literal> list. For example, consider: |
| 1782 |
<programlisting> |
| 1783 |
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...; |
| 1784 |
</programlisting> |
| 1785 |
With <varname>join_collapse_limit</varname> = 1, this |
| 1786 |
forces the planner to join A to B before joining them to other tables, |
| 1787 |
but doesn't constrain its choices otherwise. In this example, the |
| 1788 |
number of possible join orders is reduced by a factor of 5. |
| 1789 |
</para> |
| 1790 |
|
| 1791 |
<para> |
| 1792 |
Constraining the planner's search in this way is a useful technique |
| 1793 |
both for reducing planning time and for directing the planner to a |
| 1794 |
good query plan. If the planner chooses a bad join order by default, |
| 1795 |
you can force it to choose a better order via <literal>JOIN</literal> syntax |
| 1796 |
— assuming that you know of a better order, that is. Experimentation |
| 1797 |
is recommended. |
| 1798 |
</para> |
| 1799 |
|
| 1800 |
<para> |
| 1801 |
A closely related issue that affects planning time is collapsing of |
| 1802 |
subqueries into their parent query. For example, consider: |
| 1803 |
<programlisting> |
| 1804 |
SELECT * |
| 1805 |
FROM x, y, |
| 1806 |
(SELECT * FROM a, b, c WHERE something) AS ss |
| 1807 |
WHERE somethingelse; |
| 1808 |
</programlisting> |
| 1809 |
This situation might arise from use of a view that contains a join; |
| 1810 |
the view's <literal>SELECT</literal> rule will be inserted in place of the view |
| 1811 |
reference, yielding a query much like the above. Normally, the planner |
| 1812 |
will try to collapse the subquery into the parent, yielding: |
| 1813 |
<programlisting> |
| 1814 |
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse; |
| 1815 |
</programlisting> |
| 1816 |
This usually results in a better plan than planning the subquery |
| 1817 |
separately. (For example, the outer <literal>WHERE</literal> conditions might be such that |
| 1818 |
joining X to A first eliminates many rows of A, thus avoiding the need to |
| 1819 |
form the full logical output of the subquery.) But at the same time, |
| 1820 |
we have increased the planning time; here, we have a five-way join |
| 1821 |
problem replacing two separate three-way join problems. Because of the |
| 1822 |
exponential growth of the number of possibilities, this makes a big |
| 1823 |
difference. The planner tries to avoid getting stuck in huge join search |
| 1824 |
problems by not collapsing a subquery if more than <varname>from_collapse_limit</varname> |
| 1825 |
<literal>FROM</literal> items would result in the parent |
| 1826 |
query. You can trade off planning time against quality of plan by |
| 1827 |
adjusting this run-time parameter up or down. |
| 1828 |
</para> |
| 1829 |
|
| 1830 |
<para> |
| 1831 |
<xref linkend="guc-from-collapse-limit"/> and <xref |
| 1832 |
linkend="guc-join-collapse-limit"/> |
| 1833 |
are similarly named because they do almost the same thing: one controls |
| 1834 |
when the planner will <quote>flatten out</quote> subqueries, and the |
| 1835 |
other controls when it will flatten out explicit joins. Typically |
| 1836 |
you would either set <varname>join_collapse_limit</varname> equal to |
| 1837 |
<varname>from_collapse_limit</varname> (so that explicit joins and subqueries |
| 1838 |
act similarly) or set <varname>join_collapse_limit</varname> to 1 (if you want |
| 1839 |
to control join order with explicit joins). But you might set them |
| 1840 |
differently if you are trying to fine-tune the trade-off between planning |
| 1841 |
time and run time. |
| 1842 |
</para> |
| 1843 |
</sect1> |
| 1844 |
|
| 1845 |
<sect1 id="populate"> |
| 1846 |
<title>Populating a Database</title> |
| 1847 |
|
| 1848 |
<para> |
| 1849 |
One might need to insert a large amount of data when first populating |
| 1850 |
a database. This section contains some suggestions on how to make |
| 1851 |
this process as efficient as possible. |
| 1852 |
</para> |
| 1853 |
|
| 1854 |
<sect2 id="disable-autocommit"> |
| 1855 |
<title>Disable Autocommit</title> |
| 1856 |
|
| 1857 |
<indexterm> |
| 1858 |
<primary>autocommit</primary> |
| 1859 |
<secondary>bulk-loading data</secondary> |
| 1860 |
</indexterm> |
| 1861 |
|
| 1862 |
<para> |
| 1863 |
When using multiple <command>INSERT</command>s, turn off autocommit and just do |
| 1864 |
one commit at the end. (In plain |
| 1865 |
SQL, this means issuing <command>BEGIN</command> at the start and |
| 1866 |
<command>COMMIT</command> at the end. Some client libraries might |
| 1867 |
do this behind your back, in which case you need to make sure the |
| 1868 |
library does it when you want it done.) If you allow each |
| 1869 |
insertion to be committed separately, |
| 1870 |
<productname>PostgreSQL</productname> is doing a lot of work for |
| 1871 |
each row that is added. An additional benefit of doing all |
| 1872 |
insertions in one transaction is that if the insertion of one row |
| 1873 |
were to fail then the insertion of all rows inserted up to that |
| 1874 |
point would be rolled back, so you won't be stuck with partially |
| 1875 |
loaded data. |
| 1876 |
</para> |
| 1877 |
</sect2> |
| 1878 |
|
| 1879 |
<sect2 id="populate-copy-from"> |
| 1880 |
<title>Use <command>COPY</command></title> |
| 1881 |
|
| 1882 |
<para> |
| 1883 |
Use <link linkend="sql-copy"><command>COPY</command></link> to load |
| 1884 |
all the rows in one command, instead of using a series of |
| 1885 |
<command>INSERT</command> commands. The <command>COPY</command> |
| 1886 |
command is optimized for loading large numbers of rows; it is less |
| 1887 |
flexible than <command>INSERT</command>, but incurs significantly |
| 1888 |
less overhead for large data loads. Since <command>COPY</command> |
| 1889 |
is a single command, there is no need to disable autocommit if you |
| 1890 |
use this method to populate a table. |
| 1891 |
</para> |
| 1892 |
|
| 1893 |
<para> |
| 1894 |
If you cannot use <command>COPY</command>, it might help to use <link |
| 1895 |
linkend="sql-prepare"><command>PREPARE</command></link> to create a |
| 1896 |
prepared <command>INSERT</command> statement, and then use |
| 1897 |
<command>EXECUTE</command> as many times as required. This avoids |
| 1898 |
some of the overhead of repeatedly parsing and planning |
| 1899 |
<command>INSERT</command>. Different interfaces provide this facility |
| 1900 |
in different ways; look for <quote>prepared statements</quote> in the interface |
| 1901 |
documentation. |
| 1902 |
</para> |
| 1903 |
|
| 1904 |
<para> |
| 1905 |
Note that loading a large number of rows using |
| 1906 |
<command>COPY</command> is almost always faster than using |
| 1907 |
<command>INSERT</command>, even if <command>PREPARE</command> is used and |
| 1908 |
multiple insertions are batched into a single transaction. |
| 1909 |
</para> |
| 1910 |
|
| 1911 |
<para> |
| 1912 |
<command>COPY</command> is fastest when used within the same |
| 1913 |
transaction as an earlier <command>CREATE TABLE</command> or |
| 1914 |
<command>TRUNCATE</command> command. In such cases no WAL |
| 1915 |
needs to be written, because in case of an error, the files |
| 1916 |
containing the newly loaded data will be removed anyway. |
| 1917 |
However, this consideration only applies when |
| 1918 |
<xref linkend="guc-wal-level"/> is <literal>minimal</literal> |
| 1919 |
as all commands must write WAL otherwise. |
| 1920 |
</para> |
| 1921 |
|
| 1922 |
</sect2> |
| 1923 |
|
| 1924 |
<sect2 id="populate-rm-indexes"> |
| 1925 |
<title>Remove Indexes</title> |
| 1926 |
|
| 1927 |
<para> |
| 1928 |
If you are loading a freshly created table, the fastest method is to |
| 1929 |
create the table, bulk load the table's data using |
| 1930 |
<command>COPY</command>, then create any indexes needed for the |
| 1931 |
table. Creating an index on pre-existing data is quicker than |
| 1932 |
updating it incrementally as each row is loaded. |
| 1933 |
</para> |
| 1934 |
|
| 1935 |
<para> |
| 1936 |
If you are adding large amounts of data to an existing table, |
| 1937 |
it might be a win to drop the indexes, |
| 1938 |
load the table, and then recreate the indexes. Of course, the |
| 1939 |
database performance for other users might suffer |
| 1940 |
during the time the indexes are missing. One should also think |
| 1941 |
twice before dropping a unique index, since the error checking |
| 1942 |
afforded by the unique constraint will be lost while the index is |
| 1943 |
missing. |
| 1944 |
</para> |
| 1945 |
</sect2> |
| 1946 |
|
| 1947 |
<sect2 id="populate-rm-fkeys"> |
| 1948 |
<title>Remove Foreign Key Constraints</title> |
| 1949 |
|
| 1950 |
<para> |
| 1951 |
Just as with indexes, a foreign key constraint can be checked |
| 1952 |
<quote>in bulk</quote> more efficiently than row-by-row. So it might be |
| 1953 |
useful to drop foreign key constraints, load data, and re-create |
| 1954 |
the constraints. Again, there is a trade-off between data load |
| 1955 |
speed and loss of error checking while the constraint is missing. |
| 1956 |
</para> |
| 1957 |
|
| 1958 |
<para> |
| 1959 |
What's more, when you load data into a table with existing foreign key |
| 1960 |
constraints, each new row requires an entry in the server's list of |
| 1961 |
pending trigger events (since it is the firing of a trigger that checks |
| 1962 |
the row's foreign key constraint). Loading many millions of rows can |
| 1963 |
cause the trigger event queue to overflow available memory, leading to |
| 1964 |
intolerable swapping or even outright failure of the command. Therefore |
| 1965 |
it may be <emphasis>necessary</emphasis>, not just desirable, to drop and re-apply |
| 1966 |
foreign keys when loading large amounts of data. If temporarily removing |
| 1967 |
the constraint isn't acceptable, the only other recourse may be to split |
| 1968 |
up the load operation into smaller transactions. |
| 1969 |
</para> |
| 1970 |
</sect2> |
| 1971 |
|
| 1972 |
<sect2 id="populate-work-mem"> |
| 1973 |
<title>Increase <varname>maintenance_work_mem</varname></title> |
| 1974 |
|
| 1975 |
<para> |
| 1976 |
Temporarily increasing the <xref linkend="guc-maintenance-work-mem"/> |
| 1977 |
configuration variable when loading large amounts of data can |
| 1978 |
lead to improved performance. This will help to speed up <command>CREATE |
| 1979 |
INDEX</command> commands and <command>ALTER TABLE ADD FOREIGN KEY</command> commands. |
| 1980 |
It won't do much for <command>COPY</command> itself, so this advice is |
| 1981 |
only useful when you are using one or both of the above techniques. |
| 1982 |
</para> |
| 1983 |
</sect2> |
| 1984 |
|
| 1985 |
<sect2 id="populate-max-wal-size"> |
| 1986 |
<title>Increase <varname>max_wal_size</varname></title> |
| 1987 |
|
| 1988 |
<para> |
| 1989 |
Temporarily increasing the <xref linkend="guc-max-wal-size"/> |
| 1990 |
configuration variable can also |
| 1991 |
make large data loads faster. This is because loading a large |
| 1992 |
amount of data into <productname>PostgreSQL</productname> will |
| 1993 |
cause checkpoints to occur more often than the normal checkpoint |
| 1994 |
frequency (specified by the <varname>checkpoint_timeout</varname> |
| 1995 |
configuration variable). Whenever a checkpoint occurs, all dirty |
| 1996 |
pages must be flushed to disk. By increasing |
| 1997 |
<varname>max_wal_size</varname> temporarily during bulk |
| 1998 |
data loads, the number of checkpoints that are required can be |
| 1999 |
reduced. |
| 2000 |
</para> |
| 2001 |
</sect2> |
| 2002 |
|
| 2003 |
<sect2 id="populate-pitr"> |
| 2004 |
<title>Disable WAL Archival and Streaming Replication</title> |
| 2005 |
|
| 2006 |
<para> |
| 2007 |
When loading large amounts of data into an installation that uses |
| 2008 |
WAL archiving or streaming replication, it might be faster to take a |
| 2009 |
new base backup after the load has completed than to process a large |
| 2010 |
amount of incremental WAL data. To prevent incremental WAL logging |
| 2011 |
while loading, disable archiving and streaming replication, by setting |
| 2012 |
<xref linkend="guc-wal-level"/> to <literal>minimal</literal>, |
| 2013 |
<xref linkend="guc-archive-mode"/> to <literal>off</literal>, and |
| 2014 |
<xref linkend="guc-max-wal-senders"/> to zero. |
| 2015 |
But note that changing these settings requires a server restart, |
| 2016 |
and makes any base backups taken before unavailable for archive |
| 2017 |
recovery and standby server, which may lead to data loss. |
| 2018 |
</para> |
| 2019 |
|
| 2020 |
<para> |
| 2021 |
Aside from avoiding the time for the archiver or WAL sender to process the |
| 2022 |
WAL data, doing this will actually make certain commands faster, because |
| 2023 |
they do not to write WAL at all if <varname>wal_level</varname> |
| 2024 |
is <literal>minimal</literal> and the current subtransaction (or top-level |
| 2025 |
transaction) created or truncated the table or index they change. (They |
| 2026 |
can guarantee crash safety more cheaply by doing |
| 2027 |
an <function>fsync</function> at the end than by writing WAL.) |
| 2028 |
</para> |
| 2029 |
</sect2> |
| 2030 |
|
| 2031 |
<sect2 id="populate-analyze"> |
| 2032 |
<title>Run <command>ANALYZE</command> Afterwards</title> |
| 2033 |
|
| 2034 |
<para> |
| 2035 |
Whenever you have significantly altered the distribution of data |
| 2036 |
within a table, running <link linkend="sql-analyze"><command>ANALYZE</command></link> is strongly recommended. This |
| 2037 |
includes bulk loading large amounts of data into the table. Running |
| 2038 |
<command>ANALYZE</command> (or <command>VACUUM ANALYZE</command>) |
| 2039 |
ensures that the planner has up-to-date statistics about the |
| 2040 |
table. With no statistics or obsolete statistics, the planner might |
| 2041 |
make poor decisions during query planning, leading to poor |
| 2042 |
performance on any tables with inaccurate or nonexistent |
| 2043 |
statistics. Note that if the autovacuum daemon is enabled, it might |
| 2044 |
run <command>ANALYZE</command> automatically; see |
| 2045 |
<xref linkend="vacuum-for-statistics"/> |
| 2046 |
and <xref linkend="autovacuum"/> for more information. |
| 2047 |
</para> |
| 2048 |
</sect2> |
| 2049 |
|
| 2050 |
<sect2 id="populate-pg-dump"> |
| 2051 |
<title>Some Notes about <application>pg_dump</application></title> |
| 2052 |
|
| 2053 |
<para> |
| 2054 |
Dump scripts generated by <application>pg_dump</application> automatically apply |
| 2055 |
several, but not all, of the above guidelines. To restore a |
| 2056 |
<application>pg_dump</application> dump as quickly as possible, you need to |
| 2057 |
do a few extra things manually. (Note that these points apply while |
| 2058 |
<emphasis>restoring</emphasis> a dump, not while <emphasis>creating</emphasis> it. |
| 2059 |
The same points apply whether loading a text dump with |
| 2060 |
<application>psql</application> or using <application>pg_restore</application> to load |
| 2061 |
from a <application>pg_dump</application> archive file.) |
| 2062 |
</para> |
| 2063 |
|
| 2064 |
<para> |
| 2065 |
By default, <application>pg_dump</application> uses <command>COPY</command>, and when |
| 2066 |
it is generating a complete schema-and-data dump, it is careful to |
| 2067 |
load data before creating indexes and foreign keys. So in this case |
| 2068 |
several guidelines are handled automatically. What is left |
| 2069 |
for you to do is to: |
| 2070 |
<itemizedlist> |
| 2071 |
<listitem> |
| 2072 |
<para> |
| 2073 |
Set appropriate (i.e., larger than normal) values for |
| 2074 |
<varname>maintenance_work_mem</varname> and |
| 2075 |
<varname>max_wal_size</varname>. |
| 2076 |
</para> |
| 2077 |
</listitem> |
| 2078 |
<listitem> |
| 2079 |
<para> |
| 2080 |
If using WAL archiving or streaming replication, consider disabling |
| 2081 |
them during the restore. To do that, set <varname>archive_mode</varname> |
| 2082 |
to <literal>off</literal>, |
| 2083 |
<varname>wal_level</varname> to <literal>minimal</literal>, and |
| 2084 |
<varname>max_wal_senders</varname> to zero before loading the dump. |
| 2085 |
Afterwards, set them back to the right values and take a fresh |
| 2086 |
base backup. |
| 2087 |
</para> |
| 2088 |
</listitem> |
| 2089 |
<listitem> |
| 2090 |
<para> |
| 2091 |
Experiment with the parallel dump and restore modes of both |
| 2092 |
<application>pg_dump</application> and <application>pg_restore</application> and find the |
| 2093 |
optimal number of concurrent jobs to use. Dumping and restoring in |
| 2094 |
parallel by means of the <option>-j</option> option should give you a |
| 2095 |
significantly higher performance over the serial mode. |
| 2096 |
</para> |
| 2097 |
</listitem> |
| 2098 |
<listitem> |
| 2099 |
<para> |
| 2100 |
Consider whether the whole dump should be restored as a single |
| 2101 |
transaction. To do that, pass the <option>-1</option> or |
| 2102 |
<option>--single-transaction</option> command-line option to |
| 2103 |
<application>psql</application> or <application>pg_restore</application>. When using this |
| 2104 |
mode, even the smallest of errors will rollback the entire restore, |
| 2105 |
possibly discarding many hours of processing. Depending on how |
| 2106 |
interrelated the data is, that might seem preferable to manual cleanup, |
| 2107 |
or not. <command>COPY</command> commands will run fastest if you use a single |
| 2108 |
transaction and have WAL archiving turned off. |
| 2109 |
</para> |
| 2110 |
</listitem> |
| 2111 |
<listitem> |
| 2112 |
<para> |
| 2113 |
If multiple CPUs are available in the database server, consider using |
| 2114 |
<application>pg_restore</application>'s <option>--jobs</option> option. This |
| 2115 |
allows concurrent data loading and index creation. |
| 2116 |
</para> |
| 2117 |
</listitem> |
| 2118 |
<listitem> |
| 2119 |
<para> |
| 2120 |
Run <command>ANALYZE</command> afterwards. |
| 2121 |
</para> |
| 2122 |
</listitem> |
| 2123 |
</itemizedlist> |
| 2124 |
</para> |
| 2125 |
|
| 2126 |
<para> |
| 2127 |
A data-only dump will still use <command>COPY</command>, but it does not |
| 2128 |
drop or recreate indexes, and it does not normally touch foreign |
| 2129 |
keys. |
| 2130 |
|
| 2131 |
<footnote> |
| 2132 |
<para> |
| 2133 |
You can get the effect of disabling foreign keys by using |
| 2134 |
the <option>--disable-triggers</option> option — but realize that |
| 2135 |
that eliminates, rather than just postpones, foreign key |
| 2136 |
validation, and so it is possible to insert bad data if you use it. |
| 2137 |
</para> |
| 2138 |
</footnote> |
| 2139 |
|
| 2140 |
So when loading a data-only dump, it is up to you to drop and recreate |
| 2141 |
indexes and foreign keys if you wish to use those techniques. |
| 2142 |
It's still useful to increase <varname>max_wal_size</varname> |
| 2143 |
while loading the data, but don't bother increasing |
| 2144 |
<varname>maintenance_work_mem</varname>; rather, you'd do that while |
| 2145 |
manually recreating indexes and foreign keys afterwards. |
| 2146 |
And don't forget to <command>ANALYZE</command> when you're done; see |
| 2147 |
<xref linkend="vacuum-for-statistics"/> |
| 2148 |
and <xref linkend="autovacuum"/> for more information. |
| 2149 |
</para> |
| 2150 |
</sect2> |
| 2151 |
</sect1> |
| 2152 |
|
| 2153 |
<sect1 id="non-durability"> |
| 2154 |
<title>Non-Durable Settings</title> |
| 2155 |
|
| 2156 |
<indexterm zone="non-durability"> |
| 2157 |
<primary>non-durable</primary> |
| 2158 |
</indexterm> |
| 2159 |
|
| 2160 |
<para> |
| 2161 |
Durability is a database feature that guarantees the recording of |
| 2162 |
committed transactions even if the server crashes or loses |
| 2163 |
power. However, durability adds significant database overhead, |
| 2164 |
so if your site does not require such a guarantee, |
| 2165 |
<productname>PostgreSQL</productname> can be configured to run |
| 2166 |
much faster. The following are configuration changes you can make |
| 2167 |
to improve performance in such cases. Except as noted below, durability |
| 2168 |
is still guaranteed in case of a crash of the database software; |
| 2169 |
only an abrupt operating system crash creates a risk of data loss |
| 2170 |
or corruption when these settings are used. |
| 2171 |
|
| 2172 |
<itemizedlist> |
| 2173 |
<listitem> |
| 2174 |
<para> |
| 2175 |
Place the database cluster's data directory in a memory-backed |
| 2176 |
file system (i.e., <acronym>RAM</acronym> disk). This eliminates all |
| 2177 |
database disk I/O, but limits data storage to the amount of |
| 2178 |
available memory (and perhaps swap). |
| 2179 |
</para> |
| 2180 |
</listitem> |
| 2181 |
|
| 2182 |
<listitem> |
| 2183 |
<para> |
| 2184 |
Turn off <xref linkend="guc-fsync"/>; there is no need to flush |
| 2185 |
data to disk. |
| 2186 |
</para> |
| 2187 |
</listitem> |
| 2188 |
|
| 2189 |
<listitem> |
| 2190 |
<para> |
| 2191 |
Turn off <xref linkend="guc-synchronous-commit"/>; there might be no |
| 2192 |
need to force <acronym>WAL</acronym> writes to disk on every |
| 2193 |
commit. This setting does risk transaction loss (though not data |
| 2194 |
corruption) in case of a crash of the <emphasis>database</emphasis>. |
| 2195 |
</para> |
| 2196 |
</listitem> |
| 2197 |
|
| 2198 |
<listitem> |
| 2199 |
<para> |
| 2200 |
Turn off <xref linkend="guc-full-page-writes"/>; there is no need |
| 2201 |
to guard against partial page writes. |
| 2202 |
</para> |
| 2203 |
</listitem> |
| 2204 |
|
| 2205 |
<listitem> |
| 2206 |
<para> |
| 2207 |
Increase <xref linkend="guc-max-wal-size"/> and <xref |
| 2208 |
linkend="guc-checkpoint-timeout"/>; this reduces the frequency |
| 2209 |
of checkpoints, but increases the storage requirements of |
| 2210 |
<filename>/pg_wal</filename>. |
| 2211 |
</para> |
| 2212 |
</listitem> |
| 2213 |
|
| 2214 |
<listitem> |
| 2215 |
<para> |
| 2216 |
Create <link linkend="sql-createtable-unlogged">unlogged |
| 2217 |
tables</link> to avoid <acronym>WAL</acronym> writes, though it |
| 2218 |
makes the tables non-crash-safe. |
| 2219 |
</para> |
| 2220 |
</listitem> |
| 2221 |
|
| 2222 |
</itemizedlist> |
| 2223 |
</para> |
| 2224 |
</sect1> |
| 2225 |
|
| 2226 |
</chapter> |