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