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
