Skip site navigation (1) Skip section navigation (2)

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

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Stefan Keller <sfkeller(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index over all partitions (aka global index)?
Date: 2012-10-14 05:39:12
Message-ID: CAMkU=1xg5iJcuzjpj0a4Abbup-EVQ=22hCCr8SfrFYO=8UL5qA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: henk de witDate: 2012-10-14 06:55:34
Subject: Query with limit goes from few ms to hours
Previous:From: Stefan KellerDate: 2012-10-14 00:43:23
Subject: Index over all partitions (aka global index)?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group