[postgis-users] Re: Slow spatial join

[Mark Cave-Ayland]

On Thu, 2007-05-31 at 19:58 +0200, Anders Larsson wrote:
> 
> Thank you for suggestions, but unfortunately it didn´t help using
> Contains.
> The region data is a standard country map (i also tried with another
> country dataset), but same result.
> I have also tried with other point data, but same slow response.
> 
> I tried to do the join with Esri ArcMap and it finished in 5 sek! Only
> a 1/10th of the time...
> Also if I leave out the last part and only do the
> Indexed-overlaps-porthion of the question it still takes
> 15 sek - much longer than the complete question in ArcMap
> Ex: 
> SELECT occurrencies.name, country.name
> FROM occurrencies, country
> WHERE occurrencies.the_geom && country.the_geom
> 
> I have also made a fresh install of PostgreSQL 8.2 + PostGIS 1.2.1 on
> another computer and tried the join on that computer, with same slow
> result..
> I'm using windows XP sp1. and XP sp2
> 
> All my data is created with SRID -1 (does this matter for speed?)
> 
> /Anders

Strange. Your basic index query of "SELECT occurrencies.name,
country.name FROM occurrencies, country
WHERE occurrencies.the_geom && country.the_geom" should be lightning
fast. The first thing I would suspect would be that you are low on
shared buffers - what does "show shared_buffers" give? If it's only
1000, try increasing the value to 20000 in postgresql.conf and
restarting the PostgreSQL service (you can confirm the new setting with
"show shared_buffers" again) and repeating your query with just the &&
above.


Kind regards,

Mark.

-- 
ILande - Open Source Consultancy
http://www.ilande.co.uk