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

[Paragon Corporation]

Mike,

Couple of  thoughts.  Given you have so many joins, could be you are
reaching the join collapse limit and the planner is kicking out before
making an optimal plan.

Try increasing the join_collapse_limit and from_collapse_limit

As was detailed in this thread

http://archives.postgresql.org/pgsql-performance/2009-04/msg00258.php



Alternatively could be your actual and estimated costs are out of wack and
might help upping your default targets and reanalyzing data. You can
probably get a sense of this by doing a an explain analyze of your query and
comparing the actual cost/row count with the estimated cost/row count where
its doing a nested loop.

 Admittedly this hasn't helped much for us.

http://archives.postgresql.org/pgsql-performance/2009-02/msg00336.php

Leo and Regina,

http:///www.postgis.us


 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Mike
Leahy
Sent: Wednesday, March 31, 2010 1:17 PM
To: Mark Cave-Ayland
Cc: PostGIS Users Discussion
Subject: Re: [postgis-users] Nested loop join = very bad performance

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.
> 
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users