Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 931F01337BCD for ; Tue, 12 Oct 2010 18:35:13 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 94734-04 for ; Tue, 12 Oct 2010 21:35:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 26.mail-out.ovh.net (26.mail-out.ovh.net [91.121.27.225]) by mail.postgresql.org (Postfix) with SMTP id 91CC61337BD3 for ; Tue, 12 Oct 2010 18:35:05 -0300 (ADT) Received: (qmail 28410 invoked by uid 503); 12 Oct 2010 21:40:02 -0000 Received: from b6.ovh.net (HELO mail612.ha.ovh.net) (213.186.33.56) by 26.mail-out.ovh.net with SMTP; 12 Oct 2010 21:40:02 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 12 Oct 2010 23:35:02 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 12 Oct 2010 23:35:01 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "pgsql-performance@postgresql.org" , "Neil Whelchel" Subject: Re: Slow count(*) again... References: <201010091626.18636.neil.whelchel@gmail.com> <201010111254.58479.neil.whelchel@gmail.com> <4CB3A2CC.9070009@vmsinfo.com> <201010111936.46207.neil.whelchel@gmail.com> Date: Tue, 12 Oct 2010 23:35:01 +0200 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: <201010111936.46207.neil.whelchel@gmail.com> User-Agent: Opera Mail/10.62 (Linux) X-Ovh-Tracer-Id: 15313646108123596337 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N 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/182 X-Sequence-Number: 40601 > suggest that 99% instances of the "select count(*)" idiom are probably >> bad use of the SQL language. Well, suppose you paginate results. If the user sees that the search query returns 500 pages, there are two options : - you're google, and your sorting algorithms are so good that the answer the user wants is in the first page - or the user will refine his search by entering more keywords tu get a manageable result set So, in both cases, the count(*) was useless anyway. And the slowest ones are the most useless, since the user will immediatey discard the result and refine his query. If your full text search is slow, try Xapian or Lucene.