Re: hash partitioning

[William Garrison <postgres@xxxxxxxxxxxx>]

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?

Does this work with stored procs?  Ex, suppose a stored procedure like this:
    get_from_test(id int, id_mod_4 int)
    SELECT id FROM test WHERE id = $1 and id % 4 = $2;
Would the optimizer know the correct table to use in that case?

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.

 

Regards

David

 

CREATE TABLE test (

    id         int not null primary key

);

 

CREATE TABLE test_0 ( CHECK ( id % 4 = 0) ) INHERITS (test);

CREATE TABLE test_1 ( CHECK ( id % 4 = 1) ) INHERITS (test);

CREATE TABLE test_2 ( CHECK ( id % 4 = 2) ) INHERITS (test);

CREATE TABLE test_3 ( CHECK ( id % 4 = 3) ) INHERITS (test);

 

CREATE RULE test_0 AS ON INSERT TO test WHERE ( id % 4 = 0 ) DO INSTEAD INSERT INTO test_0 VALUES ( NEW.id );

CREATE RULE test_1 AS ON INSERT TO test WHERE ( id % 4 = 1 ) DO INSTEAD INSERT INTO test_1 VALUES ( NEW.id );

CREATE RULE test_2 AS ON INSERT TO test WHERE ( id % 4 = 2 ) DO INSTEAD INSERT INTO test_2 VALUES ( NEW.id );

CREATE RULE test_3 AS ON INSERT TO test WHERE ( id % 4 = 3 ) DO INSTEAD INSERT INTO test_3 VALUES ( NEW.id );

 

insert into test values(1);

 

explain analyse select * from test;

"Result  (cost=0.00..170.00 rows=12000 width=4) (actual time=0.027..0.042 rows=1 loops=1)"

"  ->  Append  (cost=0.00..170.00 rows=12000 width=4) (actual time=0.020..0.032 rows=1 loops=1)"

"        ->  Seq Scan on test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.002..0.002 rows=0 loops=1)"

"        ->  Seq Scan on test_0 test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"

"        ->  Seq Scan on test_1 test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.007..0.009 rows=1 loops=1)"

"        ->  Seq Scan on test_2 test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"

"        ->  Seq Scan on test_3 test  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.001..0.001 rows=0 loops=1)"

"Total runtime: 0.115 ms"