Re: Constraint exclusion issue

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