Hi list, I was looking for a bit of clarification on a check constraint that I have on some tables. I was following the example in the partitioning documentation http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html And got it to work, but when I tried to apply the same idea to my tables, it appeared the check constraints weren't being used. I have a master table that looks like this mydb=# \d flows Table "public.flows" Column | Type | Modifiers ------------+-----------------------------+----------- id | integer | height | integer | start_time | date | not null end_time | date | not null srcint | integer | and a bunch of inherited tables that have the following constraint CHECK (start_time >= '2008-01-01'::date AND end_time < '2008-01-01'::date) and when i do the same query (as in the documentation) on the table, I get a bunch of sequential scans in the planner, and it appears to ignore my constraints netflow=# explain SELECT count(*) FROM flows WHERE start_time >= DATE '2008-01-23' AND end_time < '2008-01-26'::date; QUERY PLAN ------------------------------------------------------------------------------------------------ Aggregate (cost=232.09..232.10 rows=1 width=0) -> Append (cost=0.00..231.26 rows=330 width=0) -> Seq Scan on flows (cost=0.00..12.02 rows=1 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_2008 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_200801 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080122 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080121 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080120 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080101 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080102 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080103 flows (cost=0.00..94.84 rows=1 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) -> Seq Scan on flows_20080104 flows (cost=0.00..15.55 rows=41 width=0) Filter: ((start_time >= '2008-01-23'::date) AND (end_time < '2008-01-26'::date)) constraint_exclusion is set to on, and the example in the documentation worked, so I'm sure it's just a confusion on my part. I ended up coming to the conclusion that the check constraints need to be on the same field??? When I changed the constraint to be CHECK (start_time >= '2008-01-01'::date AND start_time < '2008-01-01'::date) suddenly the planner started following the check and excluding the tables properly. So ultimately my question is, to be used by constraint exclusion, do the checks need to be limited to a single field? If not, can I get away with being able to use constraint exclusion while having a multi field check like I showed earlier? Thanks in advance! -Tim ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
