From: "Greg Stark" <gsstark@mit.edu>
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> Not as good as pruning partitions entirely but if you're doing a
sequential
> scan the performance hit of a few index lookups isn't a problem.
Greg, I think you've got the right idea. For large databases, though, it
won't be uncommon to have large numbers of partitions, in which case we're
not talking about a few index lookups. The database I used in my example
wasn't huge, but the table in question had over 800 partitions. A larger
database could have thousands. I suppose the importance of global indexes
depends on the sizes of the databases your target audience is running.
Here's some more detail on our real-world experience: The group made the
decision to partition some of the larger tables for better performance. The
idea that global indexes aren't useful is pretty common in the database
world, and 2 or 3 good DBAs suggested that the 'product_no' index be local.
But with the local indexes, performance on some queries was bad enough that
the group actually made the decision to switch back to unpartitioned tables.
(The performance problems came about because of the overhead involved in
searching >800 indices to find the relevant rows).
Luckily they that had the chance to work with a truly fantastic DBA (the
author of an Oracle Press performance tuning book even) before they could
switch back. He convinced them to make some of their indexes global.
Performance dramatically improved (compared with both the unpartitioned
schema, and the partitioned-and-locally-indexed schema), and they've since
stayed with partitioned tables and a mix of local and global indexes.
But once again, I think that global indexes aren't as important as the Phase
I items in any of the Phase I/Phase II breakdowns that have been proposed in
this thread.