Re: Hash partitioning.

On Tue, Jun 25, 2013 at 11:15:24AM -0400, Robert Haas wrote:
> On Tue, Jun 25, 2013 at 11:06 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >> Not really. Constraint exclusion won't kick in for a constraint like
> >> CHECK (hashme(a) % 16 == 3) and a WHERE clause of the form a = 42.
> >
> > Uh, I thought we checked the constant against every CHECK constraint and
> > only scanned partitions that matched. Why does this not work?
>
> That's a pretty fuzzy description of what we do. For this to work,
> we'd have to be able to use the predicate a = 42 to prove that
> hashme(a) % 16 = 3 is false. But we can't actually substitute 42 in
> for a and then evaluate hashme(42) % 16 = 3, because we don't know
> that the a = 42 in the WHERE clause means exact equality for all
> purposes, only that it means "has the numerically same value". For
> integers, equality under = is sufficient to prove equivalence.
>
> But for numeric values, for example, it is not. The values
> '42'::numeric and '42.0'::numeric are equal according to =(numeric,
> numeric), but they are not the same. If the hashme() function did
> something like length($1::text), it would get different answers for
> those two values. IOW, the theorem prover has no way of knowing that
> the hash function provided has semantics that are compatible with the
> opclass of the operator used in the query.

I looked at predtest.c but I can't see how we accept >= and <= ranges,
but not CHECK (a % 16 == 3). It is the '%' operator? I am not sure why
the hashme() function is there. Wouldn't it work if hashme() was an
immutable function?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +