[GENERAL] hash partitioning

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"