Thread: query plan ignoring check constraints
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
Rohit, > 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. This is called "range partitioning". We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: www.bizgres.org http://pgfoundry.org/mail/?group_id=1000107 -- Josh Berkus Aglio Database Solutions San Francisco
On Mon, Jun 20, 2005 at 10:35:24AM -0700, Josh Berkus wrote: > Rohit, > > > 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. > > This is called "range partitioning". We're working on it. You're welcome to > join the Bizgres project where most of the discussion on this feature takes > place: Why are you discussing development there? I can see in the archives that people are talking about changing page format, semantics of tuple info bits, and it's not getting to some "people that matters." -- Alvaro Herrera (<alvherre[a]surnet.cl>) La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo" (Jason Alexander)
On Mon, 2005-06-20 at 14:40 -0400, Alvaro Herrera wrote: > On Mon, Jun 20, 2005 at 10:35:24AM -0700, Josh Berkus wrote: > > Rohit, > > > > > 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. > > > > This is called "range partitioning". We're working on it. You're welcome to > > join the Bizgres project where most of the discussion on this feature takes > > place: > > Why are you discussing development there? I can see in the archives > that people are talking about changing page format, semantics of tuple > info bits, and it's not getting to some "people that matters." IRC, telephone, private mail and face-to-face have also been used to discuss development... Hackers has been used to discuss how to implement the ideas raised in other forums. Best Regards, Simon Riggs
Someone Wrote:
> 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.
Obviously, indexes on columns with a check constraint, should be
qualified with the same check constraint.
test=# CREATE TABLE test ( foo text check(foo IN ('YES','NO'))
);
CREATE TABLE
test=# CREATE INDEX text_foo_idx ON test (foo) WHERE foo IN('YES','NO');
CREATE INDEX
test=# INSERT INTO test VALUES ('YES');
INSERT 280188 1
test=# INSERT INTO test VALUES ('NO');
INSERT 280189 1
test=# INSERT INTO test VALUES ('no');
ERROR: new row for relation "test" violates check constraint
"test_foo_check"
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'YES'; QUERY
PLAN
------------------------------------------------------------------------
--------------------------------------------Index Scan using text_foo_idx on test (cost=0.00..5.82 rows=7
width=32) (actual time=0.369..0.376 rows=1 loops=1) Index Cond: (foo = 'YES'::text)Total runtime: 0.490 ms
(3 rows)
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'no'; QUERY PLAN
------------------------------------------------------------------------
------------------------Seq Scan on test (cost=0.00..25.38 rows=7 width=32) (actual
time=0.358..0.358 rows=0 loops=1) Filter: (foo = 'no'::text)Total runtime: 0.421 ms
(3 rows)
test=#
... John
> This is called "range partitioning". We're working on it. You're welcome to > join the Bizgres project where most of the discussion on this feature takes > place: > www.bizgres.org > http://pgfoundry.org/mail/?group_id=1000107 I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... Chris
KL- > I still think the fact that that discussion is taking place on a > completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) -- Josh Berkus Aglio Database Solutions San Francisco
On Mon, 20 Jun 2005, Josh Berkus wrote: > KL- > > > I still think the fact that that discussion is taking place on a > > completely non-hackers mailing list is the lamest thing ever... > > What, like phpPgAdmin? ;-) > What on earth does phpPgAdmin have to do with the backend? I'm on the list and there's nothing happening there which doesn't belong here. Gavin
> >>I still think the fact that that discussion is taking place on a >>completely non-hackers mailing list is the lamest thing ever... > > What, like phpPgAdmin? ;-) Erm. Last time I checked phpPgAdmin was a userland application, using PHP and libpq. Bizgres is proposing modifying PostgreSQL itself and getting those changes into PostgreSQL proper. Please move your discussions to -hackers. I, and many other devs have no interest in subscribing to your own little list. Chris
Josh Berkus said: > KL- > >> I still think the fact that that discussion is taking place on a >> completely non-hackers mailing list is the lamest thing ever... > > What, like phpPgAdmin? ;-) > Josh, That is not an appropriate analogy at all - range partitioning is an inherently server-side feature, while phppgadmin is a client-side admin GUI. Chris' point as I understand it is that server-side features should be discussed on -hackers, and I have some sympathy with that POV. And if you feel like mentioning plperlng in this context, I will tell you that I wouldn't do it again that way in the light of experience, even though it is arguably far more separable. cheers andrew
Doh, sorry for coming off sounding like a knob here...my point is that it's not like you guys are some sort of rogue faction implementing range partitioning against the wishes of "the Man" - it's something that I think we all agree we want in the backend, so I don't see why you are making it hard to discuss and follow the project. Keeping it among yourselves is just a recipe for a bad case of "group think"... Chris Christopher Kings-Lynne wrote: >> >>> I still think the fact that that discussion is taking place on a >>> completely non-hackers mailing list is the lamest thing ever... >> >> >> What, like phpPgAdmin? ;-) > > > Erm. Last time I checked phpPgAdmin was a userland application, using > PHP and libpq. Bizgres is proposing modifying PostgreSQL itself and > getting those changes into PostgreSQL proper. Please move your > discussions to -hackers. I, and many other devs have no interest in > subscribing to your own little list. > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: >> This is called "range partitioning". We're working on it. You're welcome to >> join the Bizgres project where most of the discussion on this feature takes >> place: >> www.bizgres.org >> http://pgfoundry.org/mail/?group_id=1000107 > I still think the fact that that discussion is taking place on a > completely non-hackers mailing list is the lamest thing ever... Any discussions at the level of changing infomask bits definitely belong on -hackers. Do not be too surprised if you get an unfriendly reception when you post low-level changes to -patches that were never previously discussed on -hackers ... regards, tom lane
Folks, > Any discussions at the level of changing infomask bits definitely belong > on -hackers. Do not be too surprised if you get an unfriendly reception > when you post low-level changes to -patches that were never previously > discussed on -hackers ... Oh, I'm not expecting this to make it into 8.1 (Simon may feel different but I'll leave those explanations to him). That's part of the reason we're doing some of this work on -bizgres; I know from experience the difficulty of discussing anything which doesn't have to do with a release after feature freeze. The other main reason for the -bizgres list is to solicit opinions from people (such as the Mondrian team) who would not subscribe to -hackers. -- Josh Berkus Aglio Database Solutions San Francisco
On Tue, Jun 21, 2005 at 09:46:50 +1000, John Hansen <john@geeknet.com.au> wrote:
> Someone Wrote:
>
> > 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.
>
> Obviously, indexes on columns with a check constraint, should be
> qualified with the same check constraint.
I think the real problem is that check constraints on tables aren't
used by the optimizer. Given that, what you have below is expected.
There has been talk about that in the past, but I haven't heard anything
recently about someone considering implenting that.
For your problem consider not using a partial index. It isn't going to
save anything if it has a constraint matching that of the table.
> test=# CREATE TABLE test (
> foo text check(foo IN ('YES','NO'))
> );
> CREATE TABLE
> test=# CREATE INDEX text_foo_idx ON test (foo) WHERE foo IN('YES','NO');
> CREATE INDEX
> test=# INSERT INTO test VALUES ('YES');
> INSERT 280188 1
> test=# INSERT INTO test VALUES ('NO');
> INSERT 280189 1
> test=# INSERT INTO test VALUES ('no');
> ERROR: new row for relation "test" violates check constraint
> "test_foo_check"
> test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'YES';
> QUERY PLAN
>
> ------------------------------------------------------------------------
> --------------------------------------------
> Index Scan using text_foo_idx on test (cost=0.00..5.82 rows=7
> width=32) (actual time=0.369..0.376 rows=1 loops=1)
> Index Cond: (foo = 'YES'::text)
> Total runtime: 0.490 ms
> (3 rows)
> test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'no';
> QUERY PLAN
>
> ------------------------------------------------------------------------
> ------------------------
> Seq Scan on test (cost=0.00..25.38 rows=7 width=32) (actual
> time=0.358..0.358 rows=0 loops=1)
> Filter: (foo = 'no'::text)
> Total runtime: 0.421 ms
> (3 rows)
> test=#
>
> ... John
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
Bruno Wolff III [mailto:bruno@wolff.to] Wrote > I think the real problem is that check constraints on tables > aren't used by the optimizer. Given that, what you have below > is expected. > There has been talk about that in the past, but I haven't > heard anything recently about someone considering implenting that. > > For your problem consider not using a partial index. It isn't > going to save anything if it has a constraint matching that > of the table. Ahh, I get it now,... If a column has a CHECK (col IN (1,2,3)) and a query says .. WHERE col = 4; then the planner should know that the query will return 0 rows, right? ... John
On Tue, Jun 21, 2005 at 21:54:34 +1000, John Hansen <john@geeknet.com.au> wrote: > Bruno Wolff III [mailto:bruno@wolff.to] Wrote > > > I think the real problem is that check constraints on tables > > aren't used by the optimizer. Given that, what you have below > > is expected. > > There has been talk about that in the past, but I haven't > > heard anything recently about someone considering implenting that. > > > > For your problem consider not using a partial index. It isn't > > going to save anything if it has a constraint matching that > > of the table. > > > Ahh, I get it now,... > > If a column has a CHECK (col IN (1,2,3)) and a query says .. WHERE col > = 4; then the planner should > know that the query will return 0 rows, right? In an ideal world yes; in the current world no. However if you have a normal index on the table, an index scan that finds no rows isn't terribly expensive. You only want to use partial indexes when they don't cover the whole table. They make sense to enforce uniqueness of a column under some condition and when you can save significant space (becuase the condition is only satisfied for a small fraction of rows).
Bruno Wolff III [mailto:bruno@wolff.to] Wrote: > You only want to use partial indexes when they don't cover > the whole table. They make sense to enforce uniqueness of a > column under some condition and when you can save significant > space (becuase the condition is only satisfied for a small > fraction of rows). Yes, I know that,. I misunderstood the original post as a request for queries NOT to use indexes where it doesn't match the table contents. .. John
On Tue, Jun 21, 2005 at 22:11:25 +1000, John Hansen <john@geeknet.com.au> wrote: > > I misunderstood the original post as a request for queries NOT to use > indexes where it doesn't match the table contents. I think that is what they were asking, but I don't think they wanted to see a sequential scan as the alternative.