Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 79D89133616B for ; Thu, 3 Feb 2011 17:51:37 -0400 (AST) 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 62389-03 for ; Thu, 3 Feb 2011 21:51:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.catalyst.net.nz (bertrand.catalyst.net.nz [202.78.240.40]) by mail.postgresql.org (Postfix) with ESMTP id 1E8CF1337B5E for ; Thu, 3 Feb 2011 17:51:29 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 6E78831C99 for ; Fri, 4 Feb 2011 10:51:27 +1300 (NZDT) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HkO1Y0BiU296 for ; Fri, 4 Feb 2011 10:51:25 +1300 (NZDT) Received: from [192.168.1.11] (122-60-64-230.jetstream.xtra.co.nz [122.60.64.230]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id 1CF75322A9 for ; Fri, 4 Feb 2011 10:51:25 +1300 (NZDT) Message-ID: <4D4B235C.1080308@catalyst.net.nz> Date: Fri, 04 Feb 2011 10:51:24 +1300 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [HACKERS] Slow count(*) again... References: <201102012247.p11Ml6u02682@momjian.us> <4D48914B.4050403@dunslane.net> <4D489AD1.1010105@vmsinfo.com> <4D499E55.4020107@vmsinfo.com> <4D49A6B9.4060608@2ndquadrant.com> <4D49C482.4010707@vmsinfo.com> <4D49F0BA.9000901@2ndquadrant.com> <4D4A16BF.1020304@vmsinfo.com> <4D4A4844.3020409@2ndquadrant.com> <4D4AD9F6.6040903@vmsinfo.com> <4D4AE16E.9040807@peak6.com> <4D4B17B4.9030406@vmsinfo.com> In-Reply-To: <4D4B17B4.9030406@vmsinfo.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.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/110 X-Sequence-Number: 42248 On 04/02/11 10:01, Mladen Gogala wrote: > In the meantime, the fire is burning. What should the hapless owner of > the database application do in the meantime? Tell the users that it > will be better in the next version? As I've said before: hints are > make it or break it point. Without hints, I cannot consider Postgres > for the mission critical projects. I am managing big databases for > living and I flatter myself that after more than two decades of doing > it, I am not too bad at it. This is somewhat of a straw man argument. This sort of query that the optimizer does badly usually gets noticed during the test cycle i.e before production, so there is some lead time to get a fix into the code, or add/subtract indexes/redesign the query concerned. The cases I've seen in production typically involve "outgrowing" optimizer parameter settings: (e.g work_mem, effective_cache_size) as the application dataset gets bigger over time. I would note that this is *more* likely to happen with hints, as they lobotomize the optimizer so it *cannot* react to dataset size or distribution changes. regards Mark