Thread: Constraint exclusion issue
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
On Sat, Jan 16, 2010 at 11:02 AM, Mathieu De Zutter
<mathieu@dezutter.org> wrote:
> 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
Shouldn't that be 'b' not "b" ?
On Sat, Jan 16, 2010 at 7:26 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sat, Jan 16, 2010 at 11:02 AM, Mathieu De Zutter
> <mathieu@dezutter.org> wrote:
>> shs-dev=# explain select * from parent where (c,n) = '("b",0)';
>> ERROR: input of anonymous composite types is not implemented
>
> Shouldn't that be 'b' not "b" ?
It is special syntax to avoid escapes:
shs-dev=# select '("b",0)'::y;
y
-------
(b,0)
shs-dev=# select ('("b,",0)'::y).c;
ERROR: value too long for type character(1)
LINE 1: select ('("b,",0)'::y).c;
2010/1/16 Mathieu De Zutter <mathieu@dezutter.org>:
> On Sat, Jan 16, 2010 at 7:26 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Sat, Jan 16, 2010 at 11:02 AM, Mathieu De Zutter
>> <mathieu@dezutter.org> wrote:
>>> shs-dev=# explain select * from parent where (c,n) = '("b",0)';
>>> ERROR: input of anonymous composite types is not implemented
>>
>> Shouldn't that be 'b' not "b" ?
>
> It is special syntax to avoid escapes:
>
> shs-dev=# select '("b",0)'::y;
> y
> -------
> (b,0)
>
To avoid escapes use E'string'.
In that case the ' will be include into the string, so it will be not more
1 char lenght.
Or something like this:
select ('(' || quote_literal('b') || ',0)')::y
> shs-dev=# select ('("b,",0)'::y).c;
> ERROR: value too long for type character(1)
> LINE 1: select ('("b,",0)'::y).c;
>
> --
--
Emanuel Calvo Franco
DBA at: www.siu.edu.ar
www.emanuelcalvofranco.com.ar
Join: http://www.thevenusproject.com/
On Sat, 2010-01-16 at 19:02 +0100, Mathieu De Zutter wrote:
> 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.
>
...
> shs-dev=# explain select * from parent where (c,n) = ('b',0);
...
> -- Ok, lets see if I can parameterize this with only one parameter... NO!
Is there a specific reason you need to parameterize it with only one
parameter? Or is it just the way you would prefer to write it?
> shs-dev=# explain select * from parent where (c,n)::y = '("b",0)'::y;
You are just expecting the optimizer to be smarter than it is. In order
to determine that the predicate above is incompatible with the CHECK
constraint on child1, the optimizer would have to decompose the record,
then translate it internally into a form where the contradiction is
apparent.
If I were to take a guess, the reason that the first form worked is that
the records were never actually formed, the predicate was rewritten into
"c = 'b' AND n = 0".
Regards,
Jeff Davis
On Sun, Jan 17, 2010 at 8:06 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Sat, 2010-01-16 at 19:02 +0100, Mathieu De Zutter wrote:
>> 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.
>>
>
> ...
>
>> shs-dev=# explain select * from parent where (c,n) = ('b',0);
>
> ...
>
>> -- Ok, lets see if I can parameterize this with only one parameter... NO!
>
> Is there a specific reason you need to parameterize it with only one
> parameter? Or is it just the way you would prefer to write it?
It would be simpler for my code because it would reduce the number of
parameters in a lot of queries. The queries themselves could be
shorted too. However, I can do without.
>> shs-dev=# explain select * from parent where (c,n)::y = '("b",0)'::y;
>
> You are just expecting the optimizer to be smarter than it is. In order
> to determine that the predicate above is incompatible with the CHECK
> constraint on child1, the optimizer would have to decompose the record,
> then translate it internally into a form where the contradiction is
> apparent.
>
> If I were to take a guess, the reason that the first form worked is that
> the records were never actually formed, the predicate was rewritten into
> "c = 'b' AND n = 0".
Can you explain this then:
OK: select * from parent where (c,n) = ('b',0);
NOT OK: select * from parent where (c,n)::y = ('b',0)::y;
Adding the row type makes it unaware of the contradiction. The strange
thing is that both queries compare with records with the same types.
The only difference is that the first uses an anonymous record type
and the second does not.
Kind regards,
Mathieu
On Sun, 2010-01-17 at 21:48 +0100, Mathieu De Zutter wrote:
> Can you explain this then:
> OK: select * from parent where (c,n) = ('b',0);
> NOT OK: select * from parent where (c,n)::y = ('b',0)::y;
Once you pass (c,n) into the cast, you get out something new that's
neither c nor n. It's as if you said "n1 + n2 = 5 + 3".
You are able to recognize that a record type is special, because you can
get the original components even after the transformation (unlike +,
which is irreversible). That allows you to transform the predicate
"(c,n)::y = ('b',0)::y" into an equivalent** form "c = 'b' AND n = 0",
which makes the contradiction with the CHECK constraint apparent.
The optimizer isn't that smart though. You're following a chain of
reasoning, and usually optimizers only go so far, because that can get
expensive, quickly.
** That's actually not really equivalent in the general case, anyway,
because of NULLs. "('a', NULL)::y = ('a', NULL)::y" is TRUE, but "'a' =
'a' AND NULL = NULL" is NULL. Interestingly, "('a', NULL) = ('a', NULL)"
is NULL as well (which might be a bug). So there isn't some nice
canonical form for "record = record" that will solve all of these
problems, which makes the optimization problem a lot harder.
Regards,
Jeff Davis