From 445ce15702f701423a0fed3defe3a43662c7cd8d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 7 Jan 2009 22:40:49 +0000 Subject: Create a third option named "partition" for constraint_exclusion, and make it the default. This setting enables constraint exclusion checks only for appendrel members (ie, inheritance children and UNION ALL arms), which are the cases in which constraint exclusion is most likely to be useful. Avoiding the overhead for simple queries that are unlikely to benefit should bring the cost down to the point where this is a reasonable default setting. Per today's discussion. --- doc/src/sgml/config.sgml | 36 +++++++++++++++++++++--------------- doc/src/sgml/ddl.sgml | 27 +++++++++++++++++++-------- doc/src/sgml/textsearch.sgml | 6 +++--- 3 files changed, 43 insertions(+), 26 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index ac30c510f2..564e5c0236 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -2145,7 +2145,7 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows - constraint_exclusion (boolean) + constraint_exclusion (enum) constraint exclusion @@ -2155,14 +2155,20 @@ archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows Enables or disables the query planner's use of table constraints to - optimize queries. The default is off. + optimize queries. + The allowed values of constraint_exclusion are + on (examine constraints for all tables), + off (never examine constraints), and + partition (examine constraints only for inheritance child + tables and UNION ALL subqueries). + partition is the default setting. - When this parameter is on, the planner compares - query conditions with table CHECK constraints, and - omits scanning tables for which the conditions contradict the - constraints. For example: + When this parameter allows it for a particular table, the planner + compares query conditions with the table's CHECK + constraints, and omits scanning tables for which the conditions + contradict the constraints. For example: CREATE TABLE parent(key integer, ...); @@ -2179,17 +2185,17 @@ SELECT * FROM parent WHERE key = 2400; - Currently, constraint_exclusion is disabled by - default because the constraint checks are relatively - expensive, and in many circumstances will yield no savings. - It is recommended to turn this on only if you are actually - using partitioned tables designed to take advantage of the - feature. + Currently, constraint exclusion is enabled by default + only for cases that are often used to implement table partitioning. + Turning it on for all tables imposes extra planning overhead that is + quite noticeable on simple queries, and most often will yield no + benefit for simple queries. If you have no partitioned tables + you might prefer to turn it off entirely. - Refer to for more information - on using constraint exclusion and partitioning. + Refer to for + more information on using constraint exclusion and partitioning. diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 94341df0f4..6c89170b34 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ - + Data Definition @@ -2135,7 +2135,7 @@ VALUES ('New York', NULL, NULL, 'NY'); an existing inheritance hierarchy, be careful to grant all the needed permissions on it. - + A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single @@ -2394,9 +2394,9 @@ CHECK ( outletID BETWEEN 200 AND 300 ) Ensure that the - configuration - parameter is enabled in postgresql.conf. Without - this, queries will not be optimized as desired. + configuration parameter is not disabled in + postgresql.conf. + If it is, queries will not be optimized as desired. @@ -2698,7 +2698,7 @@ SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; You can use the EXPLAIN command to show the difference between a plan with constraint_exclusion on and a plan - with it off. A typical default plan for this type of table setup is: + with it off. A typical unoptimized plan for this type of table setup is: SET constraint_exclusion = off; @@ -2725,7 +2725,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; full-table sequential scans, but the point here is that there is no need to scan the older partitions at all to answer this query. When we enable constraint exclusion, we get a significantly - reduced plan that will deliver the same answer: + cheaper plan that will deliver the same answer: SET constraint_exclusion = on; @@ -2751,6 +2751,17 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; be helpful in the latter case but not the former. + + The default (and recommended) setting of + is actually 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 partitioned + tables. The on setting causes the planner to examine + CHECK constraints in all queries, even simple ones that + are unlikely to benefit. + + @@ -2817,7 +2828,7 @@ UNION ALL SELECT * FROM measurement_y2008m01; Caveats - + The following caveats apply to partitioned tables: diff --git a/doc/src/sgml/textsearch.sgml b/doc/src/sgml/textsearch.sgml index ac8b75512e..4b18b06c52 100644 --- a/doc/src/sgml/textsearch.sgml +++ b/doc/src/sgml/textsearch.sgml @@ -1,4 +1,4 @@ - + Full Text Search @@ -3254,8 +3254,8 @@ SELECT plainto_tsquery('supernovae stars'); Partitioning of big collections and the proper use of GiST and GIN indexes allows the implementation of very fast searches with online update. - Partitioning can be done at the database level using table inheritance - and constraint_exclusion, or by distributing documents over + Partitioning can be done at the database level using table inheritance, + or by distributing documents over servers and collecting search results using the contrib/dblink extension module. The latter is possible because ranking functions use only local information. -- cgit v1.2.1