Re: Constraint exclusion issue

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