Re: Hash partitioning.

On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> 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?

Let me back up a minute. You told the OP that he could make hash
partitioning by writing his own constraint and trigger functions. I
think that won't work. But I'm happy to be proven wrong. Do you have
an example showing how to do it?

Here's why I think it WON'T work:

rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# create table foo0 (check ((a % 16) = 0)) inherits (foo);
CREATE TABLE
rhaas=# create table foo1 (check ((a % 16) = 1)) inherits (foo);
CREATE TABLE
rhaas=# create table foo2 (check ((a % 16) = 2)) inherits (foo);
CREATE TABLE
rhaas=# create table foo3 (check ((a % 16) = 3)) inherits (foo);
CREATE TABLE
rhaas=# explain select * from foo where a = 1;
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..101.50 rows=25 width=36)
-> Seq Scan on foo (cost=0.00..0.00 rows=1 width=36)
Filter: (a = 1)
-> Seq Scan on foo0 (cost=0.00..25.38 rows=6 width=36)
Filter: (a = 1)
-> Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36)
Filter: (a = 1)
-> Seq Scan on foo2 (cost=0.00..25.38 rows=6 width=36)
Filter: (a = 1)
-> Seq Scan on foo3 (cost=0.00..25.38 rows=6 width=36)
Filter: (a = 1)
(11 rows)

Notice we get a scan on every partition. Now let's try it with no
modulo arithmetic, just a straightforward one-partition-per-value:

rhaas=# create table foo (a int, b text);
CREATE TABLE
rhaas=# create table foo0 (check (a = 0)) inherits (foo);
CREATE TABLE
rhaas=# create table foo1 (check (a = 1)) inherits (foo);
CREATE TABLE
rhaas=# create table foo2 (check (a = 2)) inherits (foo);
CREATE TABLE
rhaas=# create table foo3 (check (a = 3)) inherits (foo);
CREATE TABLE
rhaas=# explain select * from foo where a = 1;
QUERY PLAN
------------------------------------------------------------
Append (cost=0.00..25.38 rows=7 width=36)
-> Seq Scan on foo (cost=0.00..0.00 rows=1 width=36)
Filter: (a = 1)
-> Seq Scan on foo1 (cost=0.00..25.38 rows=6 width=36)
Filter: (a = 1)
(5 rows)

Voila, now constraint exclusion is working.

I confess that I'm not entirely clear about the details either, but
the above tests speak for themselves.

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