Thread: check constraint question
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
"Tim Rupp" <caphrim007@gmail.com> writes:
> ... 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;
I don't think you've thought this through carefully. That WHERE
condition is not inconsistent with that CHECK constraint, ie, there
could be some rows in the table that meet the WHERE. In fact,
a set of constraints of this form don't represent a unique partitioning
do they? (They might if you added the additional constraint that
start_time <= end_time, but that's not explicit here.)
regards, tom lane
On Jan 24, 2008 9:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Tim Rupp" <caphrim007@gmail.com> writes: > > ... 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; > > I don't think you've thought this through carefully. That WHERE > condition is not inconsistent with that CHECK constraint, ie, there > could be some rows in the table that meet the WHERE. In fact, > a set of constraints of this form don't represent a unique partitioning > do they? (They might if you added the additional constraint that > start_time <= end_time, but that's not explicit here.) > > regards, tom lane > Thanks for the feedback. I think I can accomplish what I want to do. If I keep the current constraints CHECK (start_time >= '2008-01-01'::date AND start_time < '2008-01-02'::date) I guarantee that the end_time will always be >= the start_time, so if I wanted to select rows from between a time range, then I can have a where clause which just adds +1 to the start_time and makes it < something like where start_time >= '2008-01-01'::date AND start_time < '2008-01-10'::date should give the same rows as where start_time >= '2008-01-01'::date AND end_time <= '2008-01-09'::date but the former would need to scan much fewer tables than the latter. In any event. I think I know which direction to go. Thanks a lot Tom! -Tim
"Tim Rupp" <caphrim007@gmail.com> writes: > where start_time >= '2008-01-01'::date AND start_time < '2008-01-10'::date > > should give the same rows as > > where start_time >= '2008-01-01'::date AND end_time <= '2008-01-09'::date I think you'll need to do the same thing to the constraints as well. You'll probably want four clauses on your constraints: start_time >= ... and start_time <= ... and end_time >= ... and end_time <= ... (or BETWEEN which is equivalent) Then you could include start_time and/or end_time ranges in your where clause and the planner would prune partitions using both ranges. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!