Tom Lane wrote:
> "Florian G. Pflug" <fgp@phlo.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