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/i...titioning.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

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly