From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Index over all partitions (aka global index)? |
Date: | 2012-10-14 00:43:23 |
Message-ID: | CAFcOn293JWp5pV8Xio4s4iaoVK+zH=Ac8BtmurfkoFNaFujyNg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
Given I have a large table implemented with partitions and need fast
access to a (primary) key value in a scenario where every minute
updates (inserts/updates/deletes) are coming in.
Now since PG does not allow any index (nor constraint) on "master"
table, I have a performance issue (and a possible parallelization
opportunity).
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?
-S.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2012-10-14 05:39:12 | Re: Index over all partitions (aka global index)? |
Previous Message | Tom Lane | 2012-10-13 01:34:08 | Re: Do cast affects index usage? |