Re: On Scalability

Vincenzo Romano wrote:
> I see the main problem in the way the planner "understands" which partition
> is useful and which one is not.
> Having the DDL supporting the feature could just be syntactic sugar
> if the underlying mechanism is inadequate.
>   

You have the order of this backwards.  In order to do better than the 
way the current scheme is implemented, the optimizer needs higher 
quality metadata about the structure of the partitions to work with.  
Right now, it's inferring them from the CHECK constraints, which 
requires the whole theorem-proving bit Tom mentioned.  That's never 
going to get any more algorithmically efficient than it already is.

If the DDL that created the partitions also made better quality metadata 
available about the structure of the partitions, at that point it would 
be possible to also do better in how the optimizer pruned partitions to 
consider too.  If the list it has was known to be in a particular 
structured/sorted order, the optimizer could do a binary search to find 
relevant partitions, rather than the linear scan required right now.

Until that work is done, any other improvement attempts are doomed to 
fail.  That's the point Robert was trying to make to you.  And the fact 
Oracle does this is why it's able to scale to high partition counts 
better than PostgreSQL can.

You can read more about the work that was being done here at 
http://wiki.postgresql.org/wiki/Table_partitioning

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us