Re: how to help the planner

Marty,

* Marty Frasier (m(dot)frasier(at)escmatrix(dot)com) wrote:
> We have a particular query that takes about 75 minutes to complete. The
> selected execution plan estimates 1 row from several of the outermost
> results so picks nested loop join resolutions. That turns out to be a bad
> choice since actual row counts are in the thirty to fifty thousand range.

I've seen exactly this behaviour and it's led to many cases where we've
had to simply disable nest loop for a given query. They're usually in
functions, so that turns out to be workable without having to deal with
application changes. Still, it totally sucks.

> I haven't found where
> it's set yet but presume it was unable to determine the result set row
> count and defaulted to 1.

No.. There's no 'default to 1', afaik. The problem seems to simply be
that PG ends up estimating the number of rows coming back very poorly.
I'm actually suspicious that the number it's coming up with is much
*smaller* than one and then clamping it back to '1' as a minimum instead
of rounding it down to zero. I did see one query that moved to a nested
loop query plan from a more sensible plan when upgrading from 9.0 to
9.2, but there were plans even under 9.0 that were similairly bad.

The one thing I've not had a chance to do yet is actually build out a
test case which I can share which demonstrates this bad behaviour. If
that's something which you could provide, it would absolutely help us in
understanding and perhaps solving this issue.

Thanks!

Stephen