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
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 |