X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EF9FD563D3 for ; Mon, 21 Mar 2005 05:38:40 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59308-04 for ; Mon, 21 Mar 2005 05:38:30 +0000 (GMT) Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [204.127.198.39]) by svr1.postgresql.org (Postfix) with ESMTP id 857DB552D4 for ; Mon, 21 Mar 2005 05:38:29 +0000 (GMT) Received: from grownups (c-24-21-166-228.client.comcast.net[24.21.166.228]) by comcast.net (rwcrmhc13) with SMTP id <2005032105382801500el30fe>; Mon, 21 Mar 2005 05:38:29 +0000 Message-ID: <007401c52dd8$73395450$0200a8c0@grownups> From: "Stacy White" To: "Tom Lane" , "Greg Stark" Cc: "Alvaro Herrera" , "PFC" , "Josh Berkus" , References: <200503191202.38089.josh@agliodbs.com> <20050319225624.GA8467@dcc.uchile.cl><3404.1111277153@sss.pgh.pa.us><002a01c52d87$543d1010$0200a8c0@grownups><14172.1111359709@sss.pgh.pa.us> <871xa9fyn4.fsf@stark.xeocode.com> Subject: Re: What needs to be done for real Partitioning? Date: Sun, 20 Mar 2005 21:40:10 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.262 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200503/414 X-Sequence-Number: 11119 From: "Greg Stark" > Tom Lane 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.