Re: On Scalability

2010/10/7 Greg Smith <greg(at)2ndquadrant(dot)com>:
> 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.

Do you mean the check constraint is used as plain text to be (somehow) executed?
If this is the case, then you (all) are perfectly and obviously right
and I'm just fishing
for bicycles in the sea.

I would expect a parser to ... ehm ... parse the CHECK constraint
expression at "CREATE TABLE " time and
extract all the needed "high quality metadata", like the list of
columns involved and the type of
checks (range, value list, etc.).
The same would be useful for partial indexes, as well.

But maybe this is just wishful thinking.

> 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

Done. As well as the official documentation.
The point is that there are no hints on the topic.
There should be a "caveat" in the documentation saying that partitioning
is not scalable. As well as partial indexing.

Thanks so far for the information.

> --
> Greg Smith, 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
> PostgreSQL Training, Services and Support  www.2ndQuadrant.us

--
Vincenzo Romano at NotOrAnd Information Technologies
Software Hardware Networking Training Support Security
--
NON QVIETIS MARIBVS NAVTA PERITVS