query plan ignoring check constraints

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

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

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

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:

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.

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.