Re: Index over all partitions (aka global index)?

On Sat, Oct 13, 2012 at 5:43 PM, Stefan Keller <sfkeller(at)gmail(dot)com> wrote:
>
> Say, there is a table with 250 mio. rows split into 250 tables with 1
> mio. rows each. And say the the index behavior is O(log n). Then a
> search for a key takes O(log(250*n)) or 8.4 time units. What PG (9.1)
> currently probably does is a iterative call to all 250 partitioned
> tables, which will take O(250*log(n)) - or 1500 time units in this
> case. This is about 180 times slower.
>
> What do you think about introducing a "global index" over all
> partitions (like Ora :->)? This would be a (logically) single index
> which can be even be parallelized given the partitioned tables are
> optimally distributed like in different tablespaces.
>
> What do you think about this?

What you already have is a logically single index. What you want is
physically single index. But wouldn't that remove most of the
benefits of partitioning? You could no longer add or remove
partitions instantaneously, for example.

Cheers,

Jeff