further_enable_partition_pruning_doc_updates_v4.patch
text/plain
Filename: further_enable_partition_pruning_doc_updates_v4.patch
Type: text/plain
Part: 0
Patch
Format: unified
Series: patch v4
| File | + | − |
|---|---|---|
| doc/src/sgml/ddl.sgml | 149 | 91 |
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">