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

Yes a physical index would be one solution - but it's not the only one.

The indexes could be treated in parallel in their physical places
where they are. That's why I called it still logical.

I don't think so that I would loose all benefits of partition since an
index could adapt itself when partitions are attached or removed.
That's probably how Oracle resolves it which knows global indexes
probably since version 8(!) [1]

Yours, S.

[1] http://www.oracle-base.com/articles/8i/partitioned-tables-and-indexes.php

2012/10/14 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:
> 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