David Rowley wrote:
> Thanks for reviewing again.
Hi,
I'm thinking something a bit more radical. First, since partition
pruning is the future and constraint exclusion is soon to be a thing of
the past, we should describe pruning first, and then describe exclusion
in terms of pruning. Second, I'd put constraint exclusion as a <sect3>
inside the <sect2> that describes pruning (but keep the XML "id" the
same, so that old links continue to work.)
I took a stab at this, but ran out of time before trimming the text for
constraint exclusion. What do you think of this rough sketch? I'm
thinking 5.10.4 is close to its final form (wording suggestions of
course welcome), but 5.10.4.1 still needs to be trimmed heavily, to
avoid repeating what was already explained in 5.10.4 (we need only
explain how exclusion differs from pruning.)
I'm a bit undecided on where to leave the <note>.
(Note:
make -C doc/src/sgml html XSLTPROCFLAGS='--stringparam rootid ddl'
builds only the 'ddl' chapter, which is nice when proofreading.)
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 3f3f567222..2152b4d16d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3759,7 +3759,151 @@ ANALYZE measurement;
</sect3>
</sect2>
- <sect2 id="ddl-partitioning-constraint-exclusion">
+ <sect2 id="ddl-partition-pruning">
+ <title>Partition Pruning</title>
+
+ <indexterm>
+ <primary>partition pruning</primary>
+ </indexterm>
+
+ <para>
+ <firstterm>Partition pruning</firstterm> is a query optimization technique
+ that improves performance for partitioned tables. As an example:
+
+<programlisting>
+SET enable_partition_pruning = on; -- the default
+SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+</programlisting>
+
+ Without partition pruning, the above query would scan each of the
+ the partitions of the <structname>measurement</structname> table. With
+ partition pruning enabled, the planner will examine the definition of each
+ partition and prove that the partition need not
+ be scanned because it could not contain any rows meeting the query's
+ <literal>WHERE</literal> clause. When the planner can prove this, it
+ excludes the partition from the query plan.
+ </para>
+
+ <para>
+ You can use the <command>EXPLAIN</command> command to show the difference
+ between a plan with <varname>enable_partition_pruning</varname> on and a
plan
+ with it off. A typical unoptimized plan for this type of table setup is:
+
+<programlisting>
+SET enable_partition_pruning = off;
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+ QUERY PLAN
+───────────────────────────────────────────────────────────────────────────────────
+ Aggregate (cost=188.76..188.77 rows=1 width=8)
+ -> Append (cost=0.00..181.05 rows=3085 width=0)
+ -> Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+ -> Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+ -> Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+ -> Seq Scan on measurement_y2007m12 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+ -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+</programlisting>
+
+ Some or all of the partitions might use index scans instead of
+ full-table sequential scans, but the point here is that there
+ is no need to scan the older partitions at all to answer this query.
+ When we enable partition pruning, we get a significantly
+ cheaper plan that will deliver the same answer:
+
+<programlisting>
+SET enable_partition_pruning = on;
+EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
+ QUERY PLAN
+───────────────────────────────────────────────────────────────────────────────────
+ Aggregate (cost=37.75..37.76 rows=1 width=8)
+ -> Append (cost=0.00..36.21 rows=617 width=0)
+ -> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617
width=0)
+ Filter: (logdate >= '2008-01-01'::date)
+</programlisting>
+ </para>
+
+ <para>
+ Note that partition pruning is driven only by the constraints defined by
+ the partition keys, not by the presence of indexes. Therefore it isn't
+ necessary to define indexes on the key columns. Whether an index
+ needs to be created for a given partition depends on whether you
+ expect that queries that scan the partition will generally scan
+ a large part of the partition or just a small part. An index will
+ be helpful in the latter case but not the former.
+ </para>
+
+ <para>
+ Partition pruning
+ can be performed not only during the planning of a given query, but also
+ during its execution. This is useful as it can allow more partitions to
+ be pruned when clauses contain expressions whose values are unknown to the
+ query planner. For example, parameters defined in a
+ <command>PREPARE</command> statement, using a value obtained from a
+ subquery or using a parameterized value on the inner side of a nested loop
+ join.
+ Partition pruning during execution can be performed at any of the
+ following times:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ During initialization of the query plan. Partition pruning can be
+ performed here for parameter values which are known during the
+ initialization phase of execution. Partitions which are pruned during
+ this stage will not show up in the query's <command>EXPLAIN</command>
+ or <command>EXPLAIN ANALYZE</command>. It is possible to determine the
+ number of partitions which were removed during this phase by observing
+ the <quote>Subplans Removed</quote> property in the
+ <command>EXPLAIN</command> output.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ During actual execution of the query plan. Partition pruning may also
+ be performed here to remove partitions using values which are only
+ known during actual query execution. This includes values from
+ subqueries and values from execution-time parameters such as those from
+ parameterized nested loop joins. Since the value of these parameters
+ may change many times during the execution of the query, partition
+ pruning is performed whenever one of the execution parameters being
+ used by partition pruning changes. Determining if partitions were
+ pruned during this phase requires careful inspection of the
+ <literal>nloops</literal> property in the
+ <command>EXPLAIN ANALYZE</command> output.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ Partition pruning can be disabled using the
+ <xref linkend="guc-enable-partition-pruning"/> setting.
+ </para>
+
+ <note>
+ <para>
+ Currently, pruning of partitions during the planning of an
+ <command>UPDATE</command> or <command>DELETE</command> command is
+ implemented using the constraint exclusion method — see the
+ next section for details. However, it is still ruled by the
+ <literal>enable_partition_pruning</literal> setting instead of
+ <literal>constraint_exclusion</literal>. Also note that
+ partition pruning performed during execution is only done so for the
+ <literal>Append</literal> node type, not <literal>MergeAppend</literal>.
+ </para>
+
+ <para>
+ Both of these limitations are likely to be removed in a future release
+ of <productname>PostgreSQL</productname>.
+ </para>
+ </note>
+
+ <sect3 id="ddl-partitioning-constraint-exclusion">
<title>Inheritance Partitioning and Constraint Exclusion</title>
<indexterm>
@@ -3768,8 +3912,9 @@ ANALYZE measurement;
<para>
<firstterm>Constraint exclusion</firstterm> is a query optimization
technique
- that improves performance for inheritance partitioned tables defined in the
- fashion described above. As an example:
+ similar to partition pruning, used
+ for partitioned tables using the legacy inheritance method.
+ As an example similar to the one shown in the section above:
<programlisting>
SET constraint_exclusion = on;
@@ -3893,95 +4038,8 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
</itemizedlist>
</para>
+ </sect3>
</sect2>
-
- <sect2 id="ddl-partition-pruning">
- <title>Declarative Partitioning and Partition Pruning</title>
-
- <indexterm>
- <primary>partition pruning</primary>
- </indexterm>
-
- <para>
- <firstterm>Partition pruning</firstterm> is a query optimization technique
- similar to constraint exclusion, but applies only to declaratively
- partitioned tables. Like constraint exclusion, this uses (but is not
- limited to using) the query's <literal>WHERE</literal> clause to exclude
- partitions which cannot possibly contain any matching records.
- </para>
-
- <para>
- Partition pruning is much more efficient than constraint exclusion, since
- it avoids scanning each partition's metadata to determine if the partition
- is required for a particular query.
- </para>
-
- <para>
- Partition pruning is also more powerful than constraint exclusion as it
- can be performed not only during the planning of a given query, but also
- during its execution. This is useful as it can allow more partitions to
- be pruned when clauses contain expressions whose values are unknown to the
- query planner. For example, parameters defined in a
- <command>PREPARE</command> statement, using a value obtained from a
- subquery or using a parameterized value on the inner side of a nested loop
- join.
- </para>
-
- <para>
- Partition pruning during execution can be performed at any of the
- following times:
-
- <itemizedlist>
- <listitem>
- <para>
- During initialization of the query plan. Partition pruning can be
- performed here for parameter values which are known during the
- initialization phase of execution. Partitions which are pruned during
- this stage will not show up in the query's <command>EXPLAIN</command>
- or <command>EXPLAIN ANALYZE</command>. It is possible to determine the
- number of partitions which were removed during this phase by observing
- the <quote>Subplans Removed</quote> property in the
- <command>EXPLAIN</command> output.
- </para>
- </listitem>
-
- <listitem>
- <para>
- During actual execution of the query plan. Partition pruning may also
- be performed here to remove partitions using values which are only
- known during actual query execution. This includes values from
- subqueries and values from execution-time parameters such as those from
- parameterized nested loop joins. Since the value of these parameters
- may change many times during the execution of the query, partition
- pruning is performed whenever one of the execution parameters being
- used by partition pruning changes. Determining if partitions were
- pruned during this phase requires careful inspection of the
- <literal>nloops</literal> property in the
- <command>EXPLAIN ANALYZE</command> output.
- </para>
- </listitem>
- </itemizedlist>
- </para>
-
- <para>
- Partition pruning can be disabled using the
- <xref linkend="guc-enable-partition-pruning"/> setting.
- </para>
-
- <note>
- <para>
- Currently, pruning of partitions during the planning of an
- <command>UPDATE</command> or <command>DELETE</command> command is
- implemented using the constraint exclusion method. Only
- <command>SELECT</command> uses the partition pruning technique. Also,
- partition pruning performed during execution is only done so for the
- <literal>Append</literal> node type. Both of these limitations are
- likely to be removed in a future release of
- <productname>PostgreSQL</productname>.
- </para>
- </note>
- </sect2>
-
</sect1>
<sect1 id="ddl-foreign-data">
