Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 5F4AD1337990 for ; Sun, 13 Feb 2011 18:29:49 -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 56676-04 for ; Sun, 13 Feb 2011 22:29:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail149123.authsmtp.net (outmail149123.authsmtp.net [62.13.149.123]) by mail.postgresql.org (Postfix) with ESMTP id 5A18913364DE for ; Sun, 13 Feb 2011 18:29:41 -0400 (AST) Received: from mail-c194.authsmtp.com (mail-c194.authsmtp.com [62.13.128.121]) by punt6.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id p1DMTdhP017696 for ; Sun, 13 Feb 2011 22:29:39 GMT Received: from Sidney-Stratton.local (dsl081-245-111.sfo1.dsl.speakeasy.net [64.81.245.111]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id p1DMTb0R028035 for ; Sun, 13 Feb 2011 22:29:38 GMT Message-ID: <4D585B4C.5020602@agliodbs.com> Date: Sun, 13 Feb 2011 14:29:32 -0800 From: Josh Berkus User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Why we don't want hints References: <201102012247.p11Ml6u02682@momjian.us> <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> <4D4B3395.6010007@agliodbs.com> <4D4B394E.9080505@vmsinfo.com> <4D4B5A73.3080302@agliodbs.com> <4D53C1C0020000250003A783@gw.wicourts.gov> <87tygbyf72.fsf@cbbrowne.afilias-int.info> In-Reply-To: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Server-Quench: be9512ad-37c0-11e0-80b9-0022640b883e X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdyZgscClZXSx8a IioLCC5HRQ8+YBZL BAkbIhBDJB8RWB5f NVxfJ1xYPXUEQkpF VCReGBUITgIzDi10 aBUnOFdCYEpPXhVo UkJLS1hRHUtuBQIJ BwAcVhxsdgRHZ3x1 Z0N9Xj4bXTAHERR9 IE5WHW0FYClib2UW HhINfgZVclIeYxxC OVBiVXJYYXgPZihp WldrZWpvYW1VeHQO Txc3IABacn4mJBAb fTU3VQovDAUaSm0/ KBppNhYDFUANekUz N0c6MQAA X-Authentic-SMTP: 61633136333939.1015:706 X-AuthFastPath: 0 (Was 255) X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system. 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: 201102/356 X-Sequence-Number: 42494 I've wordsmithed Chris's changes some, and then spun off a completely separate page for Hints discussion, since the NotToDo item was becoming too long. > Something like this syntax?: > > JOIN WITH (correlation_factor=0.3) Please, NO! This is exactly the kind of hint that I regard as a last resort if we run out of implementation alternatives. Any hint which gets coded into the actual queries becomes a *massive* maintenance and upgrade headache thereafter. If we're implementing a hint alternative, we should look at stuff in this priority order: 1. Useful tuning of additional cost parameters by GUC (i.e. cursor_tuple_fraction) 2. Modifying cost parameters on database *objects* (i.e. "ndistinct=500") 3. Adding new parameters to modify on database objects (i.e. "distribution=normal(1.5,17)","new_rows=0.1") 4. Query hints (if all of the above fails to give fixes for some tested problem) > Where exactly are the problems with other systems noted? Most other > systems have this option so saying "They have problems" is a giant cop > out. I've put my list down: http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion#Problems_with_existing_Hint_stystems -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com