Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
