Re: What needs to be done for real Partitioning?
От | Stacy White |
---|---|
Тема | Re: What needs to be done for real Partitioning? |
Дата | |
Msg-id | 007401c52dd8$73395450$0200a8c0@grownups обсуждение исходный текст |
Ответ на | What needs to be done for real Partitioning? (Josh Berkus <josh@agliodbs.com>) |
Ответы |
Re: What needs to be done for real Partitioning?
|
Список | pgsql-performance |
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.
В списке pgsql-performance по дате отправления: