documentation fixes for partition pruning, round two - Mailing list pgsql-hackers

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 >= 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 >= 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)
@@ -3881,31 +3880,31 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= 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>) —
-     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>) —
+     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 >= 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 — which gives it its name — 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.