Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 637211337B95 for ; Thu, 3 Feb 2011 21:18:49 -0400 (AST) 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 53487-10 for ; Fri, 4 Feb 2011 01:18:43 +0000 (UTC) Received: from amadensor.com (amadensor.com [200.46.208.228]) by mail.postgresql.org (Postfix) with ESMTP id 21F21133740B for ; Thu, 3 Feb 2011 21:18:42 -0400 (AST) Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by amadensor.com (Postfix) with ESMTP id 6B76718A47FE for ; Fri, 4 Feb 2011 01:18:41 +0000 (UTC) Received: from amadensor.com ([200.46.208.228]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 70771-01 for ; Fri, 4 Feb 2011 01:18:34 +0000 (UTC) Received: from [192.168.2.101] (unknown [76.76.81.70]) by amadensor.com (Postfix) with ESMTPA id 0FC4918A47C9 for ; Fri, 4 Feb 2011 01:18:28 +0000 (UTC) Message-ID: <4D4B53E4.6010701@amadensor.com> Date: Thu, 03 Feb 2011 18:18:28 -0700 From: Grant Johnson 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> <4D4B1F5B.4060403@peak6.com> <4D4B261B.6010303@vmsinfo.com> <4D4B2E8A.7010402@vmsinfo.com> <4D4ADF42020000250003A34C@gw.wicourts.gov> <4D4B3B41.7060803@vmsinfo.com> <4D4B4ACE.5090908@vmsinfo.com> In-Reply-To: <4D4B4ACE.5090908@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-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/144 X-Sequence-Number: 42282 On PostgreSQL, the difference in no hints and hints for that one query with skewed data is that the query finishes a little faster. On some others, which shall remain nameless, it is the difference between finishing in seconds or days, or maybe never. Hints can be useful, but I can also see why they are not a top priority. They are rarely needed, and only when working around a bug. If you want them so badly, you have the source, write a contrib module (can you do that on Oracle or MSSQL?) If I have a choice between the developers spending time on implementing hints, and spending time on improving the optimiser, I'll take the optimiser. Tom Kyte agrees: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912905298920 http://tkyte.blogspot.com/2006/08/words-of-wisdom.html Oracle can be faster on count queries, but that is the only case I have seen. Generally on most other queries, especially when it involves complex joins, or indexes on text fields, PostgreSQL is faster on the same hardware.