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

[Mike Leahy]

Hi,

Thanks again for the suggestions.  The default values for join_collapse_limit 
and from_collapse_limit were set to 8 - I upped these both to 128, with no 
observable difference.

I guess this issue is better suited for the general PostgreSQL mailing 
lists...

Regards,
Mike

On Wednesday 31 March 2010 15:51:14 Paragon Corporation wrote:
> 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
>