Hi,
I'm trying to make constraint exclusion work correctly in a query with
only one parameter, but I have some issues.
Please have a look at the scenario below and tell me how I can improve it.
Thanks!
-- I create an inheritance relationship with a check constraint in the child
shs-dev=# create table parent (c char, n integer);
CREATE TABLE
shs-dev=# create table child1 ( ) inherits (parent);
CREATE TABLE
shs-dev=# alter table child1 add check (c = 'a');
ALTER TABLE
-- I query on a row containing both attributes, and pgsql 8.4
correctly skips the child table because of the constraint
shs-dev=# explain select * from parent where (c,n) = ('b',0);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Result (cost=0.00..39.10 rows=1 width=12)
-> Append (cost=0.00..39.10 rows=1 width=12)
-> Seq Scan on parent (cost=0.00..39.10 rows=1 width=12)
Filter: ((c = 'b'::bpchar) AND (n = 0))
-- Ok, lets see if I can parameterize this with only one parameter... NO!
shs-dev=# explain select * from parent where (c,n) = '("b",0)';
ERROR: input of anonymous composite types is not implemented
-- I create a type so it's not anonymous anymore
shs-dev=# create type y as (c char, n integer);
CREATE TYPE
-- But pgsql forgets about the constraint now :(
shs-dev=# explain select * from parent where (c,n)::y = '("b",0)'::y;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..78.20 rows=20 width=12)
-> Append (cost=0.00..78.20 rows=20 width=12)
-> Seq Scan on parent (cost=0.00..39.10 rows=10 width=12)
Filter: (ROW(c, n)::y = '(b,0)'::y)
-> Seq Scan on child1 parent (cost=0.00..39.10 rows=10 width=12)
Filter: (ROW(c, n)::y = '(b,0)'::y)
-- This is OK (but has two parameters, I want only one)
shs-dev=# explain select * from parent where ((c,n)::y).c = 'b' and
((c,n)::y).n = 0;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Result (cost=0.00..39.10 rows=1 width=12)
-> Append (cost=0.00..39.10 rows=1 width=12)
-> Seq Scan on parent (cost=0.00..39.10 rows=1 width=12)
Filter: ((c = 'b'::bpchar) AND (n = 0))
-- This isn't OK
shs-dev=# explain select * from parent where ((c,n)::y).c =
('("b",0)'::y).c and ((c,n)::y).n = ('("b",0)'::y).n;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..78.20 rows=2 width=12)
-> Append (cost=0.00..78.20 rows=2 width=12)
-> Seq Scan on parent (cost=0.00..39.10 rows=1 width=12)
Filter: ((c = ('(b,0)'::y).c) AND (n = ('(b,0)'::y).n))
-> Seq Scan on child1 parent (cost=0.00..39.10 rows=1 width=12)
Filter: ((c = ('(b,0)'::y).c) AND (n = ('(b,0)'::y).n))
-- So the problem seems to be that the 'b' value cannot be deduced in
time for the constraint exclusion to do its job.
--
Regards,
Mathieu
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
