[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Re: Slow spatial join
From:       Mark Cave-Ayland <mark.cave-ayland () ilande ! co ! uk>
Date:       2007-05-31 18:29:08
Message-ID: 1180636148.15060.32.camel () mca-desktop
[Download RAW message or body]

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


_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[prev in list] [next in list] [prev in thread] [next in thread]