codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>41.1. The Query Tree</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="rules.html" title="Chapter 41. The Rule System" /><link rel="next" href="rules-views.html" title="41.2. Views and the Rule System" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">41.1. The Query Tree</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules.html" title="Chapter 41. The Rule System">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><th width="60%" align="center">Chapter 41. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-views.html" title="41.2. Views and the Rule System">Next</a></td></tr></table><hr /></div><div class="sect1" id="QUERYTREE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.1. The Query Tree <a href="#QUERYTREE" class="id_link">#</a></h2></div></div></div><a id="id-1.8.6.6.2" class="indexterm"></a><p>3    To understand how the rule system works it is necessary to know4    when it is invoked and what its input and results are.5</p><p>6    The rule system is located between the parser and the planner.7    It takes the output of the parser, one query tree, and the user-defined8    rewrite rules, which are also9    query trees with some extra information, and creates zero or more10    query trees as result. So its input and output are always things11    the parser itself could have produced and thus, anything it sees12    is basically representable as an <acronym class="acronym">SQL</acronym> statement.13</p><p>14    Now what is a query tree? It is an internal representation of an15    <acronym class="acronym">SQL</acronym> statement where the single parts that it is16    built from are stored separately. These query trees can be shown17    in the server log if you set the configuration parameters18    <code class="varname">debug_print_parse</code>,19    <code class="varname">debug_print_rewritten</code>, or20    <code class="varname">debug_print_plan</code>.  The rule actions are also21    stored as query trees, in the system catalog22    <code class="structname">pg_rewrite</code>.  They are not formatted like23    the log output, but they contain exactly the same information.24</p><p>25    Reading a raw query tree requires some experience.  But since26    <acronym class="acronym">SQL</acronym> representations of query trees are27    sufficient to understand the rule system, this chapter will not28    teach how to read them.29</p><p>30    When reading the <acronym class="acronym">SQL</acronym> representations of the31    query trees in this chapter it is necessary to be able to identify32    the parts the statement is broken into when it is in the query tree33    structure. The parts of a query tree are34 35</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">36        the command type37    </span></dt><dd><p>38        This is a simple value telling which command39        (<code class="command">SELECT</code>, <code class="command">INSERT</code>,40        <code class="command">UPDATE</code>, <code class="command">DELETE</code>) produced41        the query tree.42    </p></dd><dt><span class="term">43        the range table44      <a id="id-1.8.6.6.7.2.2.1.1" class="indexterm"></a>45    </span></dt><dd><p>46        The range table is a list of relations that are used in the query.47        In a <code class="command">SELECT</code> statement these are the relations given after48        the <code class="literal">FROM</code> key word.49    </p><p>50        Every range table entry identifies a table or view and tells51        by which name it is called in the other parts of the query.52        In the query tree, the range table entries are referenced by53        number rather than by name, so here it doesn't matter if there54        are duplicate names as it would in an <acronym class="acronym">SQL</acronym>55        statement. This can happen after the range tables of rules56        have been merged in. The examples in this chapter will not have57        this situation.58    </p></dd><dt><span class="term">59        the result relation60    </span></dt><dd><p>61        This is an index into the range table that identifies the62        relation where the results of the query go.63    </p><p>64        <code class="command">SELECT</code> queries don't have a result65        relation. (The special case of <code class="command">SELECT INTO</code> is66        mostly identical to <code class="command">CREATE TABLE</code> followed by67        <code class="literal">INSERT ... SELECT</code>, and is not discussed68        separately here.)69    </p><p>70        For <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and71        <code class="command">DELETE</code> commands, the result relation is the table72        (or view!) where the changes are to take effect.73    </p></dd><dt><span class="term">74        the target list75    <a id="id-1.8.6.6.7.2.4.1.1" class="indexterm"></a>76    </span></dt><dd><p>77        The target list is a list of expressions that define the78        result of the query.  In the case of a79        <code class="command">SELECT</code>, these expressions are the ones that80        build the final output of the query.  They correspond to the81        expressions between the key words <code class="command">SELECT</code>82        and <code class="command">FROM</code>.  (<code class="literal">*</code> is just an83        abbreviation for all the column names of a relation.  It is84        expanded by the parser into the individual columns, so the85        rule system never sees it.)86    </p><p>87        <code class="command">DELETE</code> commands don't need a normal target list88        because they don't produce any result.  Instead, the planner89        adds a special <acronym class="acronym">CTID</acronym> entry to the empty target list,90        to allow the executor to find the row to be deleted.91        (<acronym class="acronym">CTID</acronym> is added when the result relation is an ordinary92        table.  If it is a view, a whole-row variable is added instead, by93        the rule system, as described in <a class="xref" href="rules-views.html#RULES-VIEWS-UPDATE" title="41.2.4. Updating a View">Section 41.2.4</a>.)94    </p><p>95        For <code class="command">INSERT</code> commands, the target list describes96        the new rows that should go into the result relation. It consists of the97        expressions in the <code class="literal">VALUES</code> clause or the ones from the98        <code class="command">SELECT</code> clause in <code class="literal">INSERT99        ... SELECT</code>.  The first step of the rewrite process adds100        target list entries for any columns that were not assigned to by101        the original command but have defaults.  Any remaining columns (with102        neither a given value nor a default) will be filled in by the103        planner with a constant null expression.104    </p><p>105        For <code class="command">UPDATE</code> commands, the target list106        describes the new rows that should replace the old ones. In the107        rule system, it contains just the expressions from the <code class="literal">SET108        column = expression</code> part of the command.  The planner will109        handle missing columns by inserting expressions that copy the values110        from the old row into the new one.  Just as for <code class="command">DELETE</code>,111        a <acronym class="acronym">CTID</acronym> or whole-row variable is added so that112        the executor can identify the old row to be updated.113    </p><p>114        Every entry in the target list contains an expression that can115        be a constant value, a variable pointing to a column of one116        of the relations in the range table, a parameter, or an expression117        tree made of function calls, constants, variables, operators, etc.118    </p></dd><dt><span class="term">119        the qualification120    </span></dt><dd><p>121        The query's qualification is an expression much like one of122        those contained in the target list entries. The result value of123        this expression is a Boolean that tells whether the operation124        (<code class="command">INSERT</code>, <code class="command">UPDATE</code>,125        <code class="command">DELETE</code>, or <code class="command">SELECT</code>) for the126        final result row should be executed or not. It corresponds to the <code class="literal">WHERE</code> clause127        of an <acronym class="acronym">SQL</acronym> statement.128    </p></dd><dt><span class="term">129        the join tree130    </span></dt><dd><p>131        The query's join tree shows the structure of the <code class="literal">FROM</code> clause.132        For a simple query like <code class="literal">SELECT ... FROM a, b, c</code>, the join tree is just133        a list of the <code class="literal">FROM</code> items, because we are allowed to join them in134        any order.  But when <code class="literal">JOIN</code> expressions, particularly outer joins,135        are used, we have to join in the order shown by the joins.136        In that case, the join tree shows the structure of the <code class="literal">JOIN</code> expressions.  The137        restrictions associated with particular <code class="literal">JOIN</code> clauses (from <code class="literal">ON</code> or138        <code class="literal">USING</code> expressions) are stored as qualification expressions attached139        to those join-tree nodes.  It turns out to be convenient to store140        the top-level <code class="literal">WHERE</code> expression as a qualification attached to the141        top-level join-tree item, too.  So really the join tree represents142        both the <code class="literal">FROM</code> and <code class="literal">WHERE</code> clauses of a <code class="command">SELECT</code>.143    </p></dd><dt><span class="term">144        the others145    </span></dt><dd><p>146        The other parts of the query tree like the <code class="literal">ORDER BY</code>147        clause aren't of interest here. The rule system148        substitutes some entries there while applying rules, but that149        doesn't have much to do with the fundamentals of the rule150        system.151    </p></dd></dl></div><p>152</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules.html" title="Chapter 41. The Rule System">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-views.html" title="41.2. Views and the Rule System">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 41. The Rule System </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.2. Views and the Rule System</td></tr></table></div></body></html>