Re: Is disableing nested_loops a bad idea ?

On Thu, Sep 16, 2010 at 10:13 AM, Franck Routier
<franck(dot)routier(at)axege(dot)com> wrote:
> Thanks Kevin and Samuel for your input.
>
> The point is we already made a lot of tweaking to try to tune postgresql
> to behave correctly. I work with Damien, and here is a post he did in
> july to explain the kind of problems we have
> http://comments.gmane.org/gmane.comp.db.postgresql.performance/25745
>
> The end of the thread was Robert Hass concluding that "Disabling
> nestloops altogether, even for one particular query, is
> often going to be a sledgehammer where you need a scalpel.   But then
> again, a sledgehammer is better than no hammer."
>
> So I wanted to better understand to what extend using a sledgehammer
> will impact me :-)

One particular case where you may get a nasty surprise is:

Nested Loop
-> Whatever
-> Index Scan

This isn't necessarily terrible if the would-be index scan is on a
small table, because a hash join may be not too bad. It may not be
too good, either, but if the would-be index scan is on a large table
the whole thing might turn into a merge join. That can get pretty
ugly. Of course in some cases the planner may be able to rejigger the
whole plan in some way that mitigates the damage, but not necessarily.

One of the things I've noticed about our planner is that it becomes
less predictable in stressful situations. As you increase the number
of tables involved in join planning, for example, the query planner
still delivers a lot of very good plans, but not quite as predictably.
Things don't slow down uniformly across the board; instead, most of
the plans remain pretty good but every once in a while (and with
increasing frequency as you keep cranking up the table count) you get
a bad one. Shutting off any of the enable_* constants will, I think,
produce a similar effect. Many queries can be adequate handled using
some other technique and you won't really notice it, but you may find
that you have a few (or someone will eventually write one) which
*really* needs whatever technique you turned off for decent
performance. At that point you don't have a lot of options...

Incidentally, it's "Haas", rather than "Hass".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company