Re: Hash partitioning.

Guys,
I am sorry for taking your time. The reason for my question is:
As former Oracle DBA and now simple beginner PostgreSQL DBA I would like
to say: the current partitioning mechanism might be improved. Sorry, it
seems to me far behind yesterday requirements. As model for improvement
the Oracle might be taken as example. Unfortunately I am not writing an
C code and see my benefit to PostgreSQL community in only rising this
issue. I'll be very happy to be helpful in something else, but...

Sincerely yours,


Yuri Levinsky, DBA
Celltick Technologies Ltd., 32 Maskit St., Herzliya 46733, Israel
Mobile: +972 54 6107703, Office: +972 9 9710239; Fax: +972 9 9710222


-----Original Message-----
From: Robert Haas [mailto:robertmhaas@gmail.com]
Sent: Tuesday, June 25, 2013 6:55 PM
To: Bruce Momjian
Cc: Yuri Levinsky; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] 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

This mail was received via Mail-SeCure System.