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

[Mike Leahy]

Hi Mark,

I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my system 
has a total of 4gb).  This only slightly (if at all) improves the performance, 
maybe reducing the query by somewhere around 500 ms (down to ~14700 ms).  All 
other parameters in the postgresql.conf are defaults.  I don't recall exactly 
what I changed before (I was just tried increasing memory limits and other 
things pretty much without knowing what I was doing), but none of that really 
seems to have a significant impact on the performance.

The challenge with trying to reduce this query is that the nested loop join 
only happens with the query as a whole (in general).  The briefest example I 
could put together was presented in the thread last week (see the attachment 
here: http://postgis.org/pipermail/postgis-users/2010-March/026239.html).  If 
I pull any more parameters or parts out of the query, the nest loop (and the 
resulting errors/crashes I was encountering at the time) would not happen.

Regards,
Mike


On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote:
> Mike Leahy wrote:
> > Mark/List,
> >
> > I just replaced my postgresql.conf with the default copy that appears in
> > /etc/postgresql/8.4/main/ after a fresh install.  The performance is
> > pretty much the same as before (maybe even about 400 ms worse than
> > before).
> >
> > Is there anything else I should try?
> >
> > Mike
> 
> Hi Mike,
> 
> Which parameters did you change? effective_cache_size and shared_buffers
> should be tweaked to suit the RAM available in your machine but the rest
> of the defaults are fairly sensible.
> 
> You probably want to set effective_cache_size to ~75% of your physical
> RAM and shared_buffers to ~25%. Does that make any difference at all?
> 
> Otherwise, you'll need to start breaking down your query into parts to
> see which bit is causing the slowdown. Start with the innermost query
> and then add one join at a time until you find the part which is causing
> the slowdown.
> 
> 
> ATB,
> 
> Mark.
>