query plan ignoring check constraints

From: Rohit Gaddi <rohitgaddi(at)yahoo(dot)co(dot)in>
To: pgsql-hackers(at)postgresql(dot)org
Subject: query plan ignoring check constraints
Date: 2005-06-20 14:15:08
Message-ID: 20050620141508.54305.qmail@web8307.mail.in.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

I have created a base table with a column id of type int. This table is inherited by a few subtables each of which have rows which satisfy a range of ids. The ranges are mutually exclusive. For example:

00000<=id<10000 subtable1
10000<=id<20000 subtable2
20000<=id<30000 subtable3
30000<=id<40000 subtable4
.
.
(n-1)*10000<id<n*10000 subtable_n

Additionally, I have created check constraints for each table as per their range. So subtable_i can contain ids only in the range (i-1)*10000 < id < i*10000 . The check constraints work well when i try to insert a value outside the range by disallowing such inserts. Each of the subtables have been indexed on id.

Now, when I do a select on the basetable with a range of ids, it looks up each subtable that inherits from the base table and using an indexed scan searches for values in the range. It does it even for subtables whose check constraint completely rules out the possibility of it containing any such row . Should not check constraint act as the first filter? The index should ideally be scanned only when the check constraint is passed by the search criteria but surprisingly it did not happen. The explain analyze showed cost for index scans of subtables that cannot contain rows matching the search criteria.

Is there any way I can enforce this or is there any other way of hinting the query planner to completely ignore looking at a subtable for a given range/criteria?

Thanks,
Rohit


---------------------------------
Too much spam in your inbox? Yahoo! Mail gives you the best spam protection for FREE!
http://in.mail.yahoo.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Markus Bertheau ☭ 2005-06-20 14:25:10 Re: quote_boolean() and friends missing
Previous Message Tom Lane 2005-06-20 14:10:32 Re: quote_boolean() and friends missing