diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 2cd0b8a..bae2589 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2967,70 +2967,70 @@ VALUES ('Albany', NULL, NULL, 'NY');
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 to learn more about the
ATTACH PARTITION and DETACH PARTITION
sub-commands.
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
with some exceptions, most notably:
Both CHECK and NOT NULL
constraints of a partitioned table are always inherited by all its
partitions. CHECK constraints that are marked
NO INHERIT are not allowed to be created on
partitioned tables.
Using ONLY to add or drop a constraint on only the
partitioned table is supported when there are no partitions. Once
partitions exist, using ONLY 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 TRUNCATE
+ 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 TRUNCATE
ONLY on a partitioned table will always return an
error.
Partitions cannot have columns that are not present in the parent. It
- is neither possible to specify columns when creating partitions with
- CREATE TABLE nor is it possible to add columns to
+ is not possible to specify columns when creating partitions with
+ CREATE TABLE, nor is it possible to add columns to
partitions after-the-fact using ALTER TABLE. Tables may be
added as a partition with ALTER TABLE ... ATTACH PARTITION
only if their columns exactly match the parent, including any
oid column.
You cannot drop the NOT NULL constraint on a
partition's column if the constraint is present in the parent table.
@@ -3347,37 +3347,37 @@ ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
on individual partitions, not the partitioned table.
Implementation Using Inheritance
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:
- 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.
Table inheritance allows for multiple inheritance.
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;
Partition Pruning
partition pruning
Partition pruning is a query optimization technique
- that improves performance for partitioned tables. As an example:
+ that improves performance for declaratively partitioned tables. As an example:
SET enable_partition_pruning = on; -- the default
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
Without partition pruning, the above query would scan each of the
partitions of the measurement 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
WHERE clause. When the planner can prove this, it
excludes (prunes) the partition from the query
plan.
- You can use the EXPLAIN command to show the
- difference between a plan whose partitions have been pruned from one
- whose partitions haven't, by using the
- configuration
- parameter. A typical unoptimized plan for this type of table setup
- is:
+ By using the EXPLAIN command and the configuration parameter, it's
+ possible to show the difference between a plan for which partitions have
+ been pruned and for which they have not. A typical unoptimized plan for
+ this type of table setup is:
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';
Partition pruning can be disabled using the
setting.
Currently, pruning of partitions during the planning of an
UPDATE or DELETE command is
implemented using the constraint exclusion method (however, it is
- still ruled by the enable_partition_pruning
- setting instead of constraint_exclusion) —
- see the next section for details and caveats that apply.
+ controlled by the enable_partition_pruning rather than
+ constraint_exclusion) — see the following section
+ for details and caveats that apply.
Also, execution-time partition pruning currently only occurs for the
Append node type, not MergeAppend.
Both of these behaviors are likely to be changed in a future release
of PostgreSQL.
@@ -3916,72 +3915,72 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
constraint exclusion
Constraint exclusion 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.
Constraint exclusion works in a very similar way to partition
pruning, except that it uses each table's CHECK
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 partition 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.
The fact that constraint exclusion uses CHECK
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.
The default (and recommended) setting of
is neither
on nor off, but an intermediate setting
called partition, which causes the technique to be
applied only to queries that are likely to be working on inheritance partitioned
tables. The on setting causes the planner to examine
CHECK constraints in all queries, even simple ones that
are unlikely to benefit.
The following caveats apply to constraint exclusion:
- 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.
Constraint exclusion only works when the query's WHERE
clause contains constants (or externally supplied parameters).
For example, a comparison against a non-immutable function such as
CURRENT_TIMESTAMP 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.
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.