[postgis-users] How could I improve this query?

[Benjamin Juhn]

You're current query is likely exploding your result set when table_a records intersect with more than one record in table_b.  Assuming there is no overlap of min & max values in table_b the following query will stop testing for intersection between a & c records after find one so it should run faster:  

UPDATE table_a a SET integer_field = b.integer_field
FROM table_b b
WHERE a.integer_field2 <= b.max AND a.integer_field2 >= b.min
    AND EXISTS (SELECT true FROM table_c c WHERE st_intersects(a.geom, c.geom) LIMIT 1);

also I'm assuming you meant to reference b.min here:
> table_a.integer_field2 >=
> table_a.min



On Nov 23, 2010, at 9:48 AM, David William Bitner wrote:

> Jorge,
> 
> What does running "explain analyze" give you for that query?
> 
> db
> 
> 2010/11/23 Jorge Arévalo <jorge.arevalo at deimos-space.com> 

> Hello all,
> 
> The query:
> 
> update table_a set integer_field = table_b.integer_field from table_c,
> table_b where table_b.string_field = table_c.string_field and
> table_a.integer_field2 <= table_b.max and table_a.integer_field2 >=
> table_a.min and st_intersects(table_a.geom, table_c.geom)
> 
> Context:
> - Ubuntu 10.04, PostgreSQL 8.4.5, PostGIS 1.4.0, GEOS 3.1.0, PROJ 4.7.1
> - table_a: a table with ~ 1500000 records. One geometry column of type
> POLYGON. SRID 23030, UTM coords.
> - table_b: a table with 3 integer fields and one string field. 88 records.
> - table_c: a table with ~ 69000 records. One geometry column of type
> POLYGON. SRID 23030, UTM coords.
> - Indexes: GiST on geometry columns, btree in min, max (table_b),
> 
> The query takes about 15 min to finish in a 8-core Intel(R) Xeon
> 2.50GHz, with 3 GB RAM. Would it be possible to reduce this time? I've
> changed some parameters in postgresql.conf, like "shared_buffers"
> 
> Things I think I could do (need to test it):
> - Change the string comparison by an integer-based one (I have only a
> limited set of string values).
> - Clustering geom indexes?
> - ...
> 
> Any suggestion welcome.
> 
> Thanks in advance,
> 
> --
> Jorge Arévalo
> Internet & Mobilty Division, DEIMOS
> jorge.arevalo at deimos-space.com
> http://mobility.grupodeimos.com/
> http://gis4free.wordpress.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
> -- 
> ************************************
> David William Bitner
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101123/4774b98c/attachment.html>