On Wed, Sep 3, 2008 at 10:36 AM, William Garrison <postgres@xxxxxxxxxxxx> 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. > > >
