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

[Jorge Arévalo]

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