diff options
Diffstat (limited to 'doc/src/sgml/html/planner-optimizer.html')
| -rw-r--r-- | doc/src/sgml/html/planner-optimizer.html | 111 |
1 files changed, 111 insertions, 0 deletions
diff --git a/doc/src/sgml/html/planner-optimizer.html b/doc/src/sgml/html/planner-optimizer.html new file mode 100644 index 0000000..ddacb32 --- /dev/null +++ b/doc/src/sgml/html/planner-optimizer.html @@ -0,0 +1,111 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>50.5. Planner/Optimizer</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="rule-system.html" title="50.4. The PostgreSQL Rule System" /><link rel="next" href="executor.html" title="50.6. Executor" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">50.5. Planner/Optimizer</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rule-system.html" title="50.4. The PostgreSQL Rule System">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="overview.html" title="Chapter 50. Overview of PostgreSQL Internals">Up</a></td><th width="60%" align="center">Chapter 50. Overview of PostgreSQL Internals</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="executor.html" title="50.6. Executor">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="PLANNER-OPTIMIZER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">50.5. Planner/Optimizer</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="planner-optimizer.html#id-1.10.3.8.5">50.5.1. Generating Possible Plans</a></span></dt></dl></div><p> + The task of the <em class="firstterm">planner/optimizer</em> is to + create an optimal execution plan. A given SQL query (and hence, a + query tree) can be actually executed in a wide variety of + different ways, each of which will produce the same set of + results. If it is computationally feasible, the query optimizer + will examine each of these possible execution plans, ultimately + selecting the execution plan that is expected to run the fastest. + </p><div class="note"><h3 class="title">Note</h3><p> + In some situations, examining each possible way in which a query + can be executed would take an excessive amount of time and memory + space. In particular, this occurs when executing queries + involving large numbers of join operations. In order to determine + a reasonable (not necessarily optimal) query plan in a reasonable amount + of time, <span class="productname">PostgreSQL</span> uses a <em class="firstterm">Genetic + Query Optimizer</em> (see <a class="xref" href="geqo.html" title="Chapter 59. Genetic Query Optimizer">Chapter 59</a>) when the number of joins + exceeds a threshold (see <a class="xref" href="runtime-config-query.html#GUC-GEQO-THRESHOLD">geqo_threshold</a>). + </p></div><p> + The planner's search procedure actually works with data structures + called <em class="firstterm">paths</em>, which are simply cut-down representations of + plans containing only as much information as the planner needs to make + its decisions. After the cheapest path is determined, a full-fledged + <em class="firstterm">plan tree</em> is built to pass to the executor. This represents + the desired execution plan in sufficient detail for the executor to run it. + In the rest of this section we'll ignore the distinction between paths + and plans. + </p><div class="sect2" id="id-1.10.3.8.5"><div class="titlepage"><div><div><h3 class="title">50.5.1. Generating Possible Plans</h3></div></div></div><p> + The planner/optimizer starts by generating plans for scanning each + individual relation (table) used in the query. The possible plans + are determined by the available indexes on each relation. + There is always the possibility of performing a + sequential scan on a relation, so a sequential scan plan is always + created. Assume an index is defined on a + relation (for example a B-tree index) and a query contains the + restriction + <code class="literal">relation.attribute OPR constant</code>. If + <code class="literal">relation.attribute</code> happens to match the key of the B-tree + index and <code class="literal">OPR</code> is one of the operators listed in + the index's <em class="firstterm">operator class</em>, another plan is created using + the B-tree index to scan the relation. If there are further indexes + present and the restrictions in the query happen to match a key of an + index, further plans will be considered. Index scan plans are also + generated for indexes that have a sort ordering that can match the + query's <code class="literal">ORDER BY</code> clause (if any), or a sort ordering that + might be useful for merge joining (see below). + </p><p> + If the query requires joining two or more relations, + plans for joining relations are considered + after all feasible plans have been found for scanning single relations. + The three available join strategies are: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + <em class="firstterm">nested loop join</em>: The right relation is scanned + once for every row found in the left relation. This strategy + is easy to implement but can be very time consuming. (However, + if the right relation can be scanned with an index scan, this can + be a good strategy. It is possible to use values from the current + row of the left relation as keys for the index scan of the right.) + </p></li><li class="listitem"><p> + <em class="firstterm">merge join</em>: Each relation is sorted on the join + attributes before the join starts. Then the two relations are + scanned in parallel, and matching rows are combined to form + join rows. This kind of join is more + attractive because each relation has to be scanned only once. + The required sorting might be achieved either by an explicit sort + step, or by scanning the relation in the proper order using an + index on the join key. + </p></li><li class="listitem"><p> + <em class="firstterm">hash join</em>: the right relation is first scanned + and loaded into a hash table, using its join attributes as hash keys. + Next the left relation is scanned and the + appropriate values of every row found are used as hash keys to + locate the matching rows in the table. + </p></li></ul></div><p> + </p><p> + When the query involves more than two relations, the final result + must be built up by a tree of join steps, each with two inputs. + The planner examines different possible join sequences to find the + cheapest one. + </p><p> + If the query uses fewer than <a class="xref" href="runtime-config-query.html#GUC-GEQO-THRESHOLD">geqo_threshold</a> + relations, a near-exhaustive search is conducted to find the best + join sequence. The planner preferentially considers joins between any + two relations for which there exist a corresponding join clause in the + <code class="literal">WHERE</code> qualification (i.e., for + which a restriction like <code class="literal">where rel1.attr1=rel2.attr2</code> + exists). Join pairs with no join clause are considered only when there + is no other choice, that is, a particular relation has no available + join clauses to any other relation. All possible plans are generated for + every join pair considered by the planner, and the one that is + (estimated to be) the cheapest is chosen. + </p><p> + When <code class="varname">geqo_threshold</code> is exceeded, the join + sequences considered are determined by heuristics, as described + in <a class="xref" href="geqo.html" title="Chapter 59. Genetic Query Optimizer">Chapter 59</a>. Otherwise the process is the same. + </p><p> + The finished plan tree consists of sequential or index scans of + the base relations, plus nested-loop, merge, or hash join nodes as + needed, plus any auxiliary steps needed, such as sort nodes or + aggregate-function calculation nodes. Most of these plan node + types have the additional ability to do <em class="firstterm">selection</em> + (discarding rows that do not meet a specified Boolean condition) + and <em class="firstterm">projection</em> (computation of a derived column set + based on given column values, that is, evaluation of scalar + expressions where needed). One of the responsibilities of the + planner is to attach selection conditions from the + <code class="literal">WHERE</code> clause and computation of required + output expressions to the most appropriate nodes of the plan + tree. + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rule-system.html" title="50.4. The PostgreSQL Rule System">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="overview.html" title="Chapter 50. Overview of PostgreSQL Internals">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="executor.html" title="50.6. Executor">Next</a></td></tr><tr><td width="40%" align="left" valign="top">50.4. The <span xmlns="http://www.w3.org/1999/xhtml" class="productname">PostgreSQL</span> Rule System </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 50.6. Executor</td></tr></table></div></body></html>
\ No newline at end of file |