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: [email protected] > [mailto:[email protected]] 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
