Re: Hash partitioning.

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.

>> Of course, since partitioning generally doesn't improve performance in
>> PostgreSQL anyway, it's not clear why you'd want to do this in the
>
> I think partitioning does improve performance by reducing index depth.

Generally, I think traversing an extra level of the index is cheaper
than opening extra relations and going through the theorem-prover
machinery. There are benefits to partitioning, but they have to do
with management - e.g. each partition can be vacuumed independently;
old partitions can be dropped more efficiently than you can
bulk-delete rows spread throughout a table - rather than performance.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company