Re: Constraint exclusion issue

On Sun, Jan 17, 2010 at 8:06 PM, Jeff Davis <pgsql(at)j-davis(dot)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