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