On Thu, Oct 7, 2010 at 8:10 AM, Vincenzo Romano
<vincenzo.romano@notorand.it> wrote:
> Making these things sub-linear (whether not O(log n) or even O(1) ),
> provided that there's way to, would make this RDBMS more appealing
> to enterprises.
> I mean also partial indexes (as an alternative to table partitioning).
> Being able to effectively cope with "a dozen child tables or so" it's more
> like an amateur feature.
> If you really need partitioning (or just hierarchical stuff) I think you'll need
> for quite more than a dozen items.
> If you partition by just weeks, you'll need 50+ a year.
>
> Is there any precise direction to where look into the code for it?
>
> Is there a way to put this into a wish list?
Well, you can't just arbitrarily turn a O(n) algorithm into an O(lg n)
algorithm. I think the most promising approach to scaling to large
numbers of partitions is the patch that Itagaki Takahiro was working
on back in July. Unfortunately, that patch still needs a lot of work
- and some redesign - before it will really meet our needs. Right
now, the way to set up partitioning is to create a parent table and
then create a bunch of child tables that inherit from them and then
put mutually exclusive CHECK constraints on all the children and make
sure constraint_exclusion is on so that the planner can notice when
not all children need to be scanned. As a totally general
architecture, this is probably hard to beat (or to make sublinear).
However, if we have DDL that allows the user to say: this is a set of
child tables that are range partitions on this key column, with these
boundaries, then you should be able to make the constraint exclusion
calculations much more efficient, because it won't have to infer so
much from first principles. O(lg n) doesn't seem out of the question
given that architecture.
I think, though, that that is still some way off. If you're in a
position to help with (or fund) the coding, it can be made to happen
faster, of course.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company