From 4c2d3ccf8afee7180978a3d7ef318ee7de0c0a91 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 20 Jun 2007 23:11:38 +0000 Subject: [PATCH] Add a caveat pointing out that constraint exclusion doesn't work with constraints the planner is unable to disprove, hence simple btree-compatible conditions should be used. We've seen people try to get cute with stuff like date_part(something) = something at least twice now. Even if we wanted to try to teach predtest.c about the properties of date_part, most of the useful variants aren't immutable so nothing could be proved. --- doc/src/sgml/ddl.sgml | 22 +++++++++++++++++----- 1 file changed, 17 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 33749f5f20..5525ebb5bd 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1,4 +1,4 @@ - + Data Definition @@ -2778,7 +2778,7 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be - optimized, since the planner cannot know what partitions the + optimized, since the planner cannot know which partitions the parameter value might select at run time. For the same reason, stable functions such as CURRENT_DATE must be avoided. @@ -2787,9 +2787,21 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01'; - All constraints on all partitions of the master table are considered for - constraint exclusion, so large numbers of partitions are likely to - increase query planning time considerably. + 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 btree-indexable operators. + + + + + + All constraints on all partitions of the master table are examined + during constraint exclusion, so large numbers of partitions are likely + to increase query planning time considerably. -- 2.11.0