[postgis-users] Nested loop join = very bad performance

[Mike Leahy]

Hello list,

Following up from the bug that was successfully resolved last week 
(http://trac.osgeo.org/postgis/ticket/469), I've found that nested loop joins 
are actually causing very bad performance with my queries.

Thanks to the observations of Leo and Regina in that discussion, I have found 
that I can work around this by setting enable_nestloop = off.  Once that is 
specified, queries start working about as fast.  With enable_nestloop = on, a 
query may take ~15000 ms, while enable_nestloop = off returns the same query in 
~90 ms.

I have attached a textfile showing the different 'explain analzye' results for 
the full query with/without nestloop enabled.  You could also refer to the 
somewhat simplified queries I put together in discussion thread related to bug 
#469 (see http://postgis.refractions.net/pipermail/postgis-users/2010-
March/026235.html for some examples).

Is this something that should be looked into, or should I just incorporate the 
workaround of disabling nested loop joins into my code?

Thanks,
Mike