Search Postgresql Archives

Constraint exclusion issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux