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

[Kevin Neufeld]

Interesting thought.  Note though, Benjamin, that Jorge specified 
"a.min", not "b.min" in the filter clause.
-- Kevin


On 11/23/2010 10:07 AM, Benjamin Juhn wrote:
> 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 
>> <mailto: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
>>     <mailto:jorge.arevalo at deimos-space.com>
>>     http://mobility.grupodeimos.com/
>>     http://gis4free.wordpress.com <http://gis4free.wordpress.com/>
>>     _______________________________________________
>>     postgis-users mailing list
>>     postgis-users at postgis.refractions.net
>>     <mailto: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 
>> <mailto:postgis-users at postgis.refractions.net>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> 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/c9a822cb/attachment.html>