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.