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

Reply via email to