Re: hash partitioning

On Wed, Sep 3, 2008 at 10:36 AM, William Garrison <postgres(at)mobydisk(dot)com> wrote:
> When I attended the PostgreSQL East conference, someone presented a way of
> doing this that they used for http://www.mailermailer.com/ and they did
> this:
>
> SET constraint_exclusion = on;
> EXPLAIN
> SELECT
> *
> FROM
> test
> WHERE
> id = 7
> AND id % 4 = 3
>
> Their business layer then generated the "AND id % 4 = 3" part of the SQL.
> :(
>
> Does anyone know if Oracle or any other database can handle this?
>

Oracle has support for hash partitioning like so:

CREATE TABLE sales_hash
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
week_no NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4
STORE IN (data1, data2, data3, data4);

There is no need to specify which partition to search or reference any
hash function in queries, it's all magic.

>
> David West wrote:
>
> Hi folks,
>
>
>
> I'm wondering why the postgres planner is not capable of determining the
> correct partition for a simple select for the following partitioning scheme,
> in which I'd like to automatically divide rows into four sub-tables, ie, a
> simple form of hash partitioning.
>
>
>
> Any ideas why this doesn't work, or a work around to make it work? I would
> have expected the query plan below to only query the test_1 table.
>
>
>