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

help / color / mirror / Atom feed
From: Craig James <craig_james@emolecules.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why we don't want hints Was: Slow count(*)		 again...
Date: Thu, 10 Feb 2011 10:32:31 -0800
Message-ID: <4D542F3F.60002@emolecules.com> (raw)
In-Reply-To: <4D53CA4F020000250003A789@gw.wicourts.gov>
References: <201102012247.p11Ml6u02682@momjian.us>
	<4D48914B.4050403@dunslane.net>
	<4D489AD1.1010105@vmsinfo.com>
	<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>
	<4D541B22.9080000@peak6.com>
	<4D53CA4F020000250003A789@gw.wicourts.gov>

On 2/10/11 9:21 AM, Kevin Grittner wrote:
> Shaun Thomas<sthomas@peak6.com>  wrote:
>
>> how difficult would it be to add that syntax to the JOIN
>> statement, for example?
>
> Something like this syntax?:
>
> JOIN WITH (correlation_factor=0.3)
>
> Where 1.0 might mean that for each value on the left there was only
> one distinct value on the right, and 0.0 would mean that they were
> entirely independent?  (Just as an off-the-cuff example -- I'm not
> at all sure that this makes sense, let alone is the best thing to
> specify.  I'm trying to get at *syntax* here, not particular knobs.)

There are two types of problems:

1. The optimizer is imperfect and makes a sub-optimal choice.

2. There is theoretical reasons why it's hard for the optimizer. For example, in a table with 50 columns, there is a staggering number of possible correlations.  An optimizer can't possibly figure this out, but a human might know them from the start.  The City/Postal-code correlation is a good example.

For #1, Postgres should never offer any sort of hint mechanism.  As many have pointed out, it's far better to spend the time fixing the optimizer than adding hacks.

For #2, it might make sense to give a designer a way to tell Postgres stuff that it couldn't possibly figure out. But ... not until the problem is clearly defined.

What should happen is that someone writes with an example query, and the community realizes that no amount of cleverness from Postgres could ever solve it (for solid theoretical reasons). Only then, when the problem is clearly defined, should we talk about solutions and SQL extensions.

Craig



view thread (26+ messages)  latest in thread

Message-ID: <4D542F3F.60002@emolecules.com>
Permalink:  ../4D542F3F.60002@emolecules.com/
Also on:    postgresql.org/message-id/4D542F3F.60002@emolecules.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: craig_james@emolecules.com
  Subject: Re: Why we don't want hints Was: Slow count(*)		 again...
  In-Reply-To: <4D542F3F.60002@emolecules.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