Re: Experimental evaluation of PostgreSQL's query optimizer

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> - I also can corroborate your finding that nested loop joins are often
> harmful, particularly when the inner loop is a sequential scan.
> One of the first things I do when investigating bad performance of a query
> whose plan has a nestend loop join is to set enable_nestloop to "off"
> and see if that makes a difference, and it often does.
> Maybe it would be a win to bias the planner against nested loop joins.
> This is dreaming,

Yeah, it sure is, because there's another half of the world that complains
bitterly any time they *don't* get a nested-loop plan; and for their
queries and data, they're right. I'd be the first to agree that it would
be good to make the planner smarter, but simplistic solutions like "bias
it against (or for) nested loops" aren't likely to improve matters.

> but it might be nice to have some number as to how
> reliable a certain estimate is, which is high if the estimate is, say,
> derived from a single filter on a base table and sinks as more conditions
> are involved or numbers pulled out of thin air.

That might indeed be a useful thing to try to do, though I confess I'm
not quite sure what we'd do with such numbers if we had them. It seems
like the next thing would be to replace single cost estimates for plans
with cost ranges, but then how do you compare a couple of overlapping
ranges?

regards, tom lane