Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C264B1337B86 for ; Mon, 11 Oct 2010 20:50:43 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 66884-10 for ; Mon, 11 Oct 2010 23:50:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from hrndva-omtalb.mail.rr.com (hrndva-omtalb.mail.rr.com [71.74.56.122]) by mail.postgresql.org (Postfix) with ESMTP id 57A431337B91 for ; Mon, 11 Oct 2010 20:50:36 -0300 (ADT) X-Authority-Analysis: v=1.1 cv=QISHQ+x4xS+apxgDHgAvNvlUjyrXCzYsHvuXcbPYiKg= c=1 sm=0 a=_6Iw8BWMfOIA:10 a=8nJEP1OIZ-IA:10 a=RAii6je2cdnAH6ATCOpc6g==:17 a=_Ixf_7qHAAAA:8 a=i_i-yweSyw-UTZjAxecA:9 a=f-X7pFw5mvfeb9hTG94A:7 a=5E5OMuJX2_upx3Et9Ztql0mGsk8A:4 a=wPNLvfGTeEIA:10 a=ryAo9hy1-OkA:10 a=6vsjEBoLwlAA:10 a=0tfO7MgyNwYA:10 a=_YaQ30545Z1jNr_V:21 a=iSs_TeHp3ZpnT-qv:21 a=RAii6je2cdnAH6ATCOpc6g==:117 X-Cloudmark-Score: 0 X-Originating-IP: 74.73.87.196 Received: from [74.73.87.196] ([74.73.87.196:49290] helo=[192.168.1.103]) by hrndva-oedge04.mail.rr.com (envelope-from ) (ecelerity 2.2.3.46 r()) with ESMTP id F0/17-11377-CC2A3BC4; Mon, 11 Oct 2010 23:50:36 +0000 Message-ID: <4CB3A2CC.9070009@vmsinfo.com> Date: Mon, 11 Oct 2010 19:50:36 -0400 From: Mladen Gogala User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.2.9) Gecko/20100915 Lightning/1.0b2 Thunderbird/3.1.4 MIME-Version: 1.0 To: Neil Whelchel CC: "pgsql-performance@postgresql.org" Subject: Re: Slow count(*) again... References: <201010091626.18636.neil.whelchel@gmail.com> <4CB34D69.6060108@emolecules.com> <201010111254.58479.neil.whelchel@gmail.com> In-Reply-To: <201010111254.58479.neil.whelchel@gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/118 X-Sequence-Number: 40537 On 10/11/2010 3:54 PM, Neil Whelchel wrote: > 1. A faster count(*), or something like my proposed estimate(*). > 2. A way to get the total rows matched when using LIMIT and OFFSET before > LIMIT and OFFSET are applied. The biggest single problem with "select count(*)" is that it is seriously overused. People use that idiom to establish existence, which usually leads to a performance disaster in the application using it, unless the table has no more than few hundred records. SQL language, of which PostgreSQL offers an excellent implementation, offers [NOT] EXISTS clause since its inception in the Jurassic era. The problem is with the sequential scan, not with counting. I'd even go as far as to suggest that 99% instances of the "select count(*)" idiom are probably bad use of the SQL language. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com