<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>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: </div><div><br></div><div><div>UPDATE table_a a SET integer_field = b.integer_field</div><div>FROM table_b b</div><div>WHERE a.integer_field2 <= b.max AND a.integer_field2 >= b.min</div><div> AND EXISTS (SELECT true FROM table_c c WHERE st_intersects(a.geom, c.geom) LIMIT 1);</div></div><div><br></div><div>also I'm assuming you meant to reference b.min here:</div><div><blockquote type="cite"><div><div class="gmail_quote"><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; ">table_a.integer_field2 >=<br>table_a.min</blockquote></div></div></blockquote></div><div><br></div><div><br></div><div><div>On Nov 23, 2010, at 9:48 AM, David William Bitner wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">Jorge,<div><br></div><div>What does running "explain analyze" give you for that query?</div><div><br></div><div>db<br><br><div class="gmail_quote">2010/11/23 Jorge Arévalo <span dir="ltr"><<a href="mailto:jorge.arevalo@deimos-space.com">jorge.arevalo@deimos-space.com</a>></span> </div></div></blockquote><div><div><div class="gmail_quote"><br></div></div></div><blockquote type="cite"><div><div class="gmail_quote">
<blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; position: static; z-index: auto; ">Hello all,<br>
<br>
The query:<br>
<br>
update table_a set integer_field = table_b.integer_field from table_c,<br>
table_b where table_b.string_field = table_c.string_field and<br>
table_a.integer_field2 <= table_b.max and table_a.integer_field2 >=<br>
table_a.min and st_intersects(table_a.geom, table_c.geom)<br>
<br>
Context:<br>
- Ubuntu 10.04, PostgreSQL 8.4.5, PostGIS 1.4.0, GEOS 3.1.0, PROJ 4.7.1<br>
- table_a: a table with ~ 1500000 records. One geometry column of type<br>
POLYGON. SRID 23030, UTM coords.<br>
- table_b: a table with 3 integer fields and one string field. 88 records.<br>
- table_c: a table with ~ 69000 records. One geometry column of type<br>
POLYGON. SRID 23030, UTM coords.<br>
- Indexes: GiST on geometry columns, btree in min, max (table_b),<br>
<br>
The query takes about 15 min to finish in a 8-core Intel(R) Xeon<br>
2.50GHz, with 3 GB RAM. Would it be possible to reduce this time? I've<br>
changed some parameters in postgresql.conf, like "shared_buffers"<br>
<br>
Things I think I could do (need to test it):<br>
- Change the string comparison by an integer-based one (I have only a<br>
limited set of string values).<br>
- Clustering geom indexes?<br>
- ...<br>
<br>
Any suggestion welcome.<br>
<br>
Thanks in advance,<br>
<br>
--<br>
Jorge Arévalo<br>
Internet & Mobilty Division, DEIMOS<br>
<a href="mailto:jorge.arevalo@deimos-space.com">jorge.arevalo@deimos-space.com</a><br>
<a href="http://mobility.grupodeimos.com/" target="_blank">http://mobility.grupodeimos.com/</a><br>
<a href="http://gis4free.wordpress.com/" target="_blank">http://gis4free.wordpress.com</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>************************************<br>David William Bitner<br>
</div>
_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote></div><br></body></html>