documentation fixes for partition pruning, round two

On Fri, May 18, 2018 at 08:56:53PM -0500, Justin Pryzby wrote:
> I reread this and have some more comments.
> https://www.postgresql.org/docs/devel/static/ddl-partitioning.html

> Let me know if it's useful to provide a patch.

I propose this.

There's two other, wider changes to consider:

- should "5.10.4. Partition Pruning" be moved after "5.10.2. Declarative
Partitioning", rather than after "5.10.3. Implementation Using Inheritance" ?
- should we find a unified term for "inheritence-based partitioning" and avoid
using the word "partitioning" in that context? For example: "Partitioning
can be implemented using table inheritance[...]". One possible phrase
currently begin used is: "legacy inheritance method".

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 2cd0b8a..6e1ade9 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2967,70 +2967,70 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>

<para>
It is not possible to turn a regular table into a partitioned table or
vice versa. However, it is possible to add a regular or partitioned table
containing data as a partition of a partitioned table, or remove a
partition from a partitioned table turning it into a standalone table;
see <xref linkend="sql-altertable"/> to learn more about the
<command>ATTACH PARTITION</command> and <command>DETACH PARTITION</command>
sub-commands.
</para>

<para>
Individual partitions are linked to the partitioned table with inheritance
- behind-the-scenes; however, it is not possible to use some of the
- inheritance features discussed in the previous section with partitioned
- tables and partitions. For example, a partition cannot have any parents
+ behind-the-scenes; however, it is not possible to use some of the generic
+ features of inheritance (discussed below) with declaratively partitioned
+ tables or their partitions For example, a partition cannot have any parents
other than the partitioned table it is a partition of, nor can a regular
- table inherit from a partitioned table making the latter its parent.
- That means partitioned tables and partitions do not participate in
- inheritance with regular tables. Since a partition hierarchy consisting
- of the partitioned table and its partitions is still an inheritance
- hierarchy, all the normal rules of inheritance apply as described in
+ table inherit from a partitioned table making the latter its parent. That
+ means partitioned tables and partitions do not participate in inheritance
+ with regular tables. Since a partition hierarchy consisting of the
+ partitioned table and its partitions is still an inheritance hierarchy, all
+ the normal rules of inheritance apply as described in
<xref linkend="ddl-inherit"/> with some exceptions, most notably:

<itemizedlist>
<listitem>
<para>
Both <literal>CHECK</literal> and <literal>NOT NULL</literal>
constraints of a partitioned table are always inherited by all its
partitions. <literal>CHECK</literal> constraints that are marked
<literal>NO INHERIT</literal> are not allowed to be created on
partitioned tables.
</para>
</listitem>

<listitem>
<para>
Using <literal>ONLY</literal> to add or drop a constraint on only the
partitioned table is supported when there are no partitions. Once
partitions exist, using <literal>ONLY</literal> will result in an error
as adding or dropping constraints on only the partitioned table, when
- partitions exist, is not supported. Instead, constraints can be added
- or dropped, when they are not present in the parent table, directly on
- the partitions. As a partitioned table does not have any data
- directly, attempts to use <command>TRUNCATE</command>
+ partitions exist, is not supported. Instead, constraints on the
+ partitions themselves can be added and (if they are not present in the
+ parent table) dropped. As a partitioned table does not
+ have any data directly, attempts to use <command>TRUNCATE</command>
<literal>ONLY</literal> on a partitioned table will always return an
error.
</para>
</listitem>

<listitem>
<para>
Partitions cannot have columns that are not present in the parent. It
- is neither possible to specify columns when creating partitions with
- <command>CREATE TABLE</command> nor is it possible to add columns to
+ is not possible to specify columns when creating partitions with
+ <command>CREATE TABLE</command>, nor is it possible to add columns to
partitions after-the-fact using <command>ALTER TABLE</command>. Tables may be
added as a partition with <command>ALTER TABLE ... ATTACH PARTITION</command>
only if their columns exactly match the parent, including any
<literal>oid</literal> column.
</para>
</listitem>

<listitem>
<para>
You cannot drop the <literal>NOT NULL</literal> constraint on a
partition's column if the constraint is present in the parent table.
</para>
</listitem>
</itemizedlist>
@@ -3347,37 +3347,37 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
on individual partitions, not the partitioned table.
</para>
</listitem>
</itemizedlist>
</para>
</sect3>
</sect2>

<sect2 id="ddl-partitioning-implementation-inheritance">
<title>Implementation Using Inheritance</title>
<para>
While the built-in declarative partitioning is suitable for most
common use cases, there are some circumstances where a more flexible
approach may be useful. Partitioning can be implemented using table
- inheritance, which allows for several features which are not supported
+ inheritance, which allows for several features not supported
by declarative partitioning, such as:

<itemizedlist>
<listitem>
<para>
- Partitioning enforces a rule that all partitions must have exactly
- the same set of columns as the parent, but table inheritance allows
- children to have extra columns not present in the parent.
+ For declarative partitioning, partitions must have exactly the same set
+ of columns as the partitioned table, whereas with table inheritance,
+ child tables may have extra columns not present in the parent.
</para>
</listitem>

<listitem>
<para>
Table inheritance allows for multiple inheritance.
</para>
</listitem>

<listitem>
<para>
Declarative partitioning only supports range, list and hash
partitioning, whereas table inheritance allows data to be divided in a
manner of the user's choosing. (Note, however, that if constraint
@@ -3757,52 +3757,51 @@ ANALYZE measurement;
</itemizedlist>
</para>
</sect3>
</sect2>

<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:
+ that improves performance for declaratively partitioned tables. As an example:

<programlisting>
SET enable_partition_pruning = on; -- the default
SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
</programlisting>

Without partition pruning, the above query would scan each of 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 (<firstterm>prunes</firstterm>) the partition from the query
plan.
</para>

<para>
- You can use the <command>EXPLAIN</command> command to show the
- difference between a plan whose partitions have been pruned from one
- whose partitions haven't, by using the
- <xref linkend="guc-enable-partition-pruning"/> configuration
- parameter. A typical unoptimized plan for this type of table setup
- is:
+ By using the EXPLAIN command and the <xref
+ linkend="guc-enable-partition-pruning"/> configuration parameter, it's
+ possible to show the difference between a plan whose partitions have been
+ pruned and one whose partitions haven't. A typical unoptimized plan for
+ this type of table setup is:
<programlisting>
SET enable_partition_pruning = off;
EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
QUERY PLAN
───────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=188.76..188.77 rows=1 width=8)
-&gt; Append (cost=0.00..181.05 rows=3085 width=0)
-&gt; Seq Scan on measurement_y2006m02 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate &gt;= '2008-01-01'::date)
-&gt; Seq Scan on measurement_y2006m03 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate &gt;= '2008-01-01'::date)
...
-&gt; Seq Scan on measurement_y2007m11 (cost=0.00..33.12 rows=617 width=0)
Filter: (logdate &gt;= '2008-01-01'::date)
@@ -3881,31 +3880,31 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
</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 (however, it is
- still ruled by the <literal>enable_partition_pruning</literal>
- setting instead of <literal>constraint_exclusion</literal>) &mdash;
- see the next section for details and caveats that apply.
+ controlled ruled by the <literal>enable_partition_pruning</literal>
+ rather than <literal>constraint_exclusion</literal>) &mdash;
+ see the following section for details and caveats that apply.
</para>

<para>
Also, execution-time partition pruning currently only occurs for the
<literal>Append</literal> node type, not <literal>MergeAppend</literal>.
</para>

<para>
Both of these behaviors are likely to be changed in a future release
of <productname>PostgreSQL</productname>.
</para>
</note>
</sect2>

@@ -3916,72 +3915,72 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate &gt;= DATE '2008-01-01';
<primary>constraint exclusion</primary>
</indexterm>

<para>
<firstterm>Constraint exclusion</firstterm> is a query optimization
technique similar to partition pruning. While it is primarily used
for partitioned tables using the legacy inheritance method, it can be
used for other purposes, including with declarative partitioning.
</para>

<para>
Constraint exclusion works in a very similar way to partition
pruning, except that it uses each table's <literal>CHECK</literal>
constraints &mdash; which gives it its name &mdash; whereas partition
- pruning uses the table's partitioning constraint, which exists only in
- the case of declarative partitioning. Another difference is that it
- is only applied at plan time; there is no attempt to remove
- partitions at execution time.
+ pruning uses the table's partitioning bounds, which exists only in
+ the case of declarative partitioning. Another difference is that
+ constraint exclusion is only applied at plan time; there is no attempt to
+ remove partitions at execution time.
</para>

<para>
The fact that constraint exclusion uses <literal>CHECK</literal>
constraints, which makes it slow compared to partition pruning, can
sometimes be used as an advantage: because constraints can be defined
even on declaratively-partitioned tables, in addition to the internal
partitioning constraints, and only constraint exclusion would be able
to elide certain partitions from the query plan using those.
</para>

<para>
The default (and recommended) setting of
<xref linkend="guc-constraint-exclusion"/> is 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 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:

<itemizedlist>
<listitem>
<para>
- Constraint exclusion is only applied during query planning; it is
- not applied at execution time like partition pruning does.
+ Constraint exclusion is only applied during query planning; unlike
+ partition pruning, it cannot be not applied during execution.
</para>
</listitem>

<listitem>
<para>
Constraint exclusion only works when the query's <literal>WHERE</literal>
clause contains constants (or externally supplied parameters).
For example, a comparison against a non-immutable function such as
<function>CURRENT_TIMESTAMP</function> cannot be optimized, since the
- planner cannot know which partition the function value might fall
+ planner cannot know which partition the function's value might fall
into at run time.
</para>
</listitem>

<listitem>
<para>
Keep the partitioning constraints simple, else the planner may not be
able to prove that partitions don't need to be visited. Use simple
equality conditions for list partitioning, or simple
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, because only B-tree-indexable
column(s) are allowed in the partition key.