Re: Hash partitioning.

On Tue, Jun 25, 2013 at 11:45 AM, Bruce Momjian <bruce@momjian.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@momjian.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
onfoo  (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)  ->
SeqScan on foo2  (cost=0.00..25.38 rows=6 width=36)        Filter: (a = 1)  ->  Seq Scan on foo3  (cost=0.00..25.38
rows=6width=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