pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why we don't want hints
Date: Sun, 13 Feb 2011 14:29:32 -0800
Message-ID: <4D585B4C.5020602@agliodbs.com> (raw)
In-Reply-To: <AANLkTinJjH4838Aa3wq6VrNm+MqB6hOKwC_-iei-eEj-@mail.gmail.com>
References: <201102012247.p11Ml6u02682@momjian.us>
	<AANLkTiknZi97dDwm7sHqy0EL7tpNEP9sY5uPV6AN+q=u@mail.gmail.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>
	<AANLkTi=6_7N4EuT-trfPbxb9UmanJsnL+ineO-iJUFtJ@mail.gmail.com>
	<4D4B2E8A.7010402@vmsinfo.com>
	<4D4B3395.6010007@agliodbs.com>
	<4D4B394E.9080505@vmsinfo.com>
	<4D4B5A73.3080302@agliodbs.com>
	<AANLkTi=mgusFAd-iU7w0G+_R3MJbJxeSFeU73HiB7b8i@mail.gmail.com>
	<4D53C1C0020000250003A783@gw.wicourts.gov>
	<AANLkTinrzwbAapmtmxr1_8eOXhUcgPWSFCuH5Gt2qkqf@mail.gmail.com>
	<87tygbyf72.fsf@cbbrowne.afilias-int.info>
	<AANLkTinJjH4838Aa3wq6VrNm+MqB6hOKwC_-iei-eEj-@mail.gmail.com>

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



view thread (5+ messages)  latest in thread

Message-ID: <4D585B4C.5020602@agliodbs.com>
Permalink:  ../4D585B4C.5020602@agliodbs.com/
Also on:    postgresql.org/message-id/4D585B4C.5020602@agliodbs.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: Why we don't want hints
  In-Reply-To: <4D585B4C.5020602@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox