On 2018/05/09 13:14, Amit Langote wrote:
> Hi David.
>
> Thanks for addressing my comments.
>
> On 2018/05/07 15:00, David Rowley wrote:
>> v2 patch is attached.
>
> Looks good to me.
Sorry, I should've seen noticed v3 before sending my email.
v3 looks good too, but when going through it, I noticed one bit in 5.10.4.
Partitioning and Constraint Exclusion:
A good rule of thumb is that partitioning constraints should
contain only comparisons of the partitioning column(s) to constants
using B-tree-indexable operators, which applies even to partitioned
tables, because only B-tree-indexable column(s) are allowed in the
partition key.
I think the part after ", which applies even to partitioned tables,.."
should be removed.
Attached find the updated patch.
Thanks,
Amit
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index ffea744cb8..76606a8535 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -3841,6 +3841,11 @@ ANY <replaceable
class="parameter">num_sync</replaceable> ( <replaceable class="
executor to remove (ignore) partitions during query execution. The
default is <literal>on</literal>.
</para>
+
+ <para>
+ Refer to <xref linkend="ddl-partition-pruning"/> for more information
+ on partition pruning and partitioning.
+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 004ecacbbf..d02edd771f 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3760,7 +3760,7 @@ ANALYZE measurement;
</sect2>
<sect2 id="ddl-partitioning-constraint-exclusion">
- <title>Partitioning and Constraint Exclusion</title>
+ <title>Inheritance Partitioning and Constraint Exclusion</title>
<indexterm>
<primary>constraint exclusion</primary>
@@ -3768,9 +3768,8 @@ ANALYZE measurement;
<para>
<firstterm>Constraint exclusion</firstterm> is a query optimization
technique
- that improves performance for partitioned tables defined in the
- fashion described above (both declaratively partitioned tables and those
- implemented using inheritance). As an example:
+ that improves performance for inheritance partitioned tables defined in the
+ fashion described above. As an example:
<programlisting>
SET constraint_exclusion = on;
@@ -3847,15 +3846,14 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
<xref linkend="guc-constraint-exclusion"/> is actually neither
<literal>on</literal> nor <literal>off</literal>, but an intermediate
setting
called <literal>partition</literal>, which causes the technique to be
- applied only to queries that are likely to be working on partitioned
+ applied only to queries that are likely to be working on inheritance
partitioned
tables. The <literal>on</literal> setting causes the planner to examine
<literal>CHECK</literal> constraints in all queries, even simple ones that
are unlikely to benefit.
</para>
<para>
- The following caveats apply to constraint exclusion, which is used by
- both inheritance and partitioned tables:
+ The following caveats apply to constraint exclusion:
<itemizedlist>
<listitem>
@@ -3877,11 +3875,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
range tests for range partitioning, as illustrated in the preceding
examples. A good rule of thumb is that partitioning constraints should
contain only comparisons of the partitioning column(s) to constants
- using B-tree-indexable operators, which applies even to partitioned
- tables, because only B-tree-indexable column(s) are allowed in the
- partition key. (This is not a problem when using declarative
- partitioning, since the automatically generated constraints are simple
- enough to be understood by the planner.)
+ using B-tree-indexable operators.
</para>
</listitem>
@@ -3898,6 +3892,94 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate
>= DATE '2008-01-01';
</itemizedlist>
</para>
</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">
