Re: check constraint question

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