<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Interesting thought. Note though, Benjamin, that Jorge specified
"a.min", not "b.min" in the filter clause.<br>
-- Kevin<br>
<br>
<br>
On 11/23/2010 10:07 AM, Benjamin Juhn wrote:
<blockquote
cite="mid:BD586F9C-D66E-4F94-BFE5-628B406D98E1@gmail.com"
type="cite">
<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: 0px 0px 0px
0.8ex; border-left: 1px solid rgb(204, 204, 204);
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 moz-do-not-send="true"
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: 0px 0px 0px
0.8ex; border-left: 1px solid rgb(204, 204, 204);
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 moz-do-not-send="true"
href="mailto:jorge.arevalo@deimos-space.com">jorge.arevalo@deimos-space.com</a><br>
<a moz-do-not-send="true"
href="http://mobility.grupodeimos.com/"
target="_blank">http://mobility.grupodeimos.com/</a><br>
<a moz-do-not-send="true"
href="http://gis4free.wordpress.com/" target="_blank">http://gis4free.wordpress.com</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a moz-do-not-send="true"
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 moz-do-not-send="true"
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
<br>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>
<a class="moz-txt-link-freetext" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>