Oliver, I'm still a bit baffled about these results. Haven't seen PostgreSQL behave this way even with multi-joins.
As far as settings go, for this type of thing -- upping work_mem seems to work the best for me. Other settings I tend to up are As you stated shared_buffers, Temp_buffers (I'm not absolutely sure about this but I think when PostgreSQL materializes work tables that go to disk it uses temp_buffer space and its lack there of may affect its decision to do so. This is just my unfounded opinion though so would be nice if someone corrected me or confirmed). Also when you are done, make sure to restart the service as I don't think these settings take effect until you restart. Hope that helps, Regina (Regina Obe). I have not changed the postgresql.conf file although I am interested in what settings you would change, especially considering the small amount of the data; FYI the Asia shapefile is ~600k - quite big for this dataset but still pretty small. I did experiment with increasing the shared_buffer size to 512MB. The Windows install that I currently have, PostgreSQL 8.2, has the following postgis_full_version() output: "POSTGIS="1.3.5" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS" My Ubuntu machine has "POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS" CLEARLY NOT GEOS 3.1. I guess that could be the primary cause of my problem? It doesn't appear that 3.1 is available on Windows. I guess it will be easiest for me to compile PostgreSQL on Ubuntu and then apply the latest PostGIS? Comments welcome :) ----------------------------------------- ************************************************************************** The information contained in the EMail and any attachments is confidential and intended solely and for the attention and use of the named addressee(s). It may not be disclosed to any other person without the express authority of the HPA, or the intended recipient, or both. If you are not the intended recipient, you must not disclose, copy, distribute or retain this message or any part of it. This footnote also confirms that this EMail has been swept for computer viruses, but please re-sweep any attachments before opening or saving. HTTP://www.HPA.org.uk ************************************************************************** _______________________________________________ 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
