[postgis-users] Help with Bad Query Plan

[Paragon Corporation]

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
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users