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

[David William Bitner]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20101123/12822365/attachment.html>