Re: An Idea for planner hints

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: An Idea for planner hints
Date: 2006-08-09 18:48:30
Message-ID: 44DA2DFE.10101@phlo.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Tom Lane wrote:
> "Florian G. Pflug" <fgp(at)phlo(dot)org> writes:
>> Image a complex, autogenerated query with looks something like this
>> select ....
>> from t1
>> join t2 on ...
>> join t3 on ...
>> join t4 on ...
>> ...
>> ...
>> where
>> <big, complicated expression derived from some user input>.
>
>> This big, complicated expression looks different for every query - and
>> currently, postgres often vastly overestimates the selectivity of this
>> expression.
>
> This is a straw man. There is no way that your application can throw in
> a chosen-at-random selectivity value for a join condition that it
> doesn't understand and have that be more likely to be right than the
> planner's guess.

No, my application probably won't get it right, _but_
.) I can at least _choose_ what selectivity to use. My experience is
that a selectivity that is too small (meaning that postgres
underestimates the number of records resulting for a join or where)
is usually much worse than a overly large selectivity (meaning that
postgres expects more records than it actually finds). Forcing a
high selectivity (thus letting postgres expect a lot of records)
therefore should lead to better plans then letting postgres
underestimating the selectivity.

.) Often, my application (or I) *can* guess betten then postgres. My
application, for example, executes the same set of about 100 queries
every day to build cache tables. Since I _know_ how many records the
query returned yesterday, I can use that value to get a *very*
good approximation of the selectivity. This is something my app
can do easily, while postgres would have really a hard time to figure
that out.

greetings, Florian Pflug

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message David Fetter 2006-08-09 18:48:41 Re: new job
Previous Message Jim C. Nasby 2006-08-09 18:42:05 Re: PostgreSQL performance enhancement when query planner fails to