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"
