What would be 'normal' processing time for a large postgis query

Question

I have two datasets which I want to spatially join (with an intersect). The first dataset consists of 9 million addresses. The second dataset consists of 12.000 neighbourhoods. I want the add the neighbourhood to the address.

Some information about the system: Microsoft Windows Server 2003 (SP-2) Inter(R) Xeon(R) CPU X5650 @ 2.67 GHz 2.66 GHz, 4,00 GB of RAM

I log in via Remote Desktop

The processing time for this query is about 50 minutes. Quite long I would say. Can someone tell what would be the general processing time?

Both geometry columns have an index. First I add a column

ALTER TABLE temp.test ADD COLUMN buurtnr INTEGER; 

Then I want to fill the column by doing a spatial join.

UPDATE temp.adresses a SET buurtnr = n.buurtnr FROM temp.neighb n WHERE st_intersects (a.geometrie, n.the_geom) 

I have to run the query monthly, together with similar query. The whole process now takes about a day. I want to make it faster.

Answer 1

Maybe try to do this without using an UPDATE. Since you're joining all the records in one table to another just do a create table query to make a brand new up to date table. My suspicion is that the update query could be driving the point/polygon join backwards and not nesting the loop with polygons outside points, thereby missing a big performance optimization.

CREATE TABLE addresses2 AS
SELECT a.*, n.neighborhood
FROM addresses a JOIN neighborhoods n 
ON ST_Contains(n.geom, a.geom);

Answer 2

I assume you have spatial indexes in place already. If you don't then do it. You also want to make sure your spatial indexes are being used with

EXPLAIN yourquery

ST_Intersects is not that costly especially in newer versions (version 1.4+). It is a function of how many points you have in your neighborhood how costly it is. With large number of points, ST_DWithin often performs better than ST_Intersects, so you can try switching your ST_Intersects to ST_Dwithin(geom1,geom2, 0.000001)

You can gain quite a lot of performance with simplification which will reduce the number of points regardless of which relationship function you are using. - check out our slides http://www.postgis.us/downloads/oscon2009_PostGISTips.pdf Slide 18.

So first thing to do is if you are running < PostGIS 1.4 upgrade. Major improvement.

The other thing that makes things slow is how big of an area each of your neighborhood covers as if most of your geometries don't pass the index check, they'd never even hit the more costly _ST_Intersects, _ST_DWithin checks.

Answer 3

St_intersects is costly.

This previous question shows another performance problem with St_intersects. This and this links also report that kind of problem. In a previous work I used St_intersects to eventually check the intersection between 20 and 100 polygons it usually took about 90 seconds (I think that's slow).