Re: Query Optimization with Partitioned Tables

> Note: I just learned from this change that with the 3x2 additional
> tables from the 2 new partitioned table the query of 8 becomes one with
> 14 joined tables

It seems then that you did not partition your tables in such a way
that your query only needs to scan one of them?
That is where partitioning becomes worth wile: when you have to scan
fewer and smaller tables.
You need to have a check constraint that enforces the partitioning
attribute, and you need to set constraint_exclusion to true in
postgresql.conf (or to "partition" if you use 8.4).
Then in your query if you use a predicate that corresponds to the
check constraint, the planner will skip the other tables, since it
knows that the desired data cannot be in there.

So for example if you make one table per month, you partition by the
field "the_date" (date). (make one empty parent table and inheriting
child tables)
You have a check constraint that says (the_date >= '20100101'::date
AND the_date < '20100201'::date)
Then, when you query for data in January of 2010 only this table would
be scanned. So that should not add any more tables to your query plan.

maybe a bit basic, but i hop e it helps.

cheers,

WBL

--
"Patriotism is the conviction that your country is superior to all
others because you were born in it." -- George Bernard Shaw