|
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" |
