diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 2cd0b8a..31f3438 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 whose partitions have been + pruned and one whose partitions haven't. 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 ruled 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 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. 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.