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

[Mark Cave-Ayland]

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.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs