Results from the EXPLAIN ANALYZE:<div><br></div><div><div>"Nested Loop †(cost=0.00..1189.72 rows=3941 width=87790) (actual time=7.091..2524830.264 rows=54145 loops=1)"</div><div>" †Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)"</div>
<div>" †-> †Seq Scan on cshapeswdate †(cost=0.00..16.23 rows=22 width=87304) (actual time=0.011..0.542 rows=72 loops=1)"</div><div>" † † † †Filter: ((gwsyear <= 1946::numeric) AND (gweyear >= 1946::numeric) AND (startdate <= '1946-01-01'::date))"</div>
<div>" †-> †Index Scan using idx_priogrid_land_cell on priogrid_land †(cost=0.00..8.29 rows=1 width=486) (actual time=3.026..30.152 rows=1338 loops=72)"</div><div>" † † † †Index Cond: (priogrid_land.cell && cshapeswdate.geom)"</div>
<div>"Total runtime: 2524889.630 ms"</div><div><br></div><br><div class="gmail_quote">2011/3/8 Andreas ForÝ Tollefsen <span dir="ltr"><<a href="mailto:andreasft@gmail.com">andreasft@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Hi,<div>This query takes about 41 minutes per year. Doing this for every year from 1946 to 2008 takes a lot of time.</div>
<div>The grid consists of 64818 cell polygons, while the country table has about 210 polygons.</div>
<div><br></div><div>I will add indexes on year and date and try again. An do an explain analyze.</div><div><br></div><div>Cheers,</div><div>Andreas<br><br><div class="gmail_quote">2011/3/8 Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span><br>

<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div></div><div class="h5">



<div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Andreas,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">I don't see anything glaringly wrong with your query, but hard 
to tell without seeing an explain plan or what you mean by takes a lot of 
time.† Is a lot of time minutes, hours, days?</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">How many records are we talking about here?† What's the 
max number of points you have in any geometry.† Often times its just one 
huge mega geometry with a†100,000 points or more slowing everything 
down.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span>†</div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Do you have indexes on your year fields and date 
columns?</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span>†</div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Regina </font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"><a href="http://www.postgis.us" target="_blank">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span>†</div><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font size="2" face="Tahoma"><b>From:</b> 
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a> 
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>On Behalf Of 
</b>Andreas ForÝ Tollefsen<br><b>Sent:</b> Tuesday, March 08, 2011 4:15 
AM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> [postgis-users] 
Increase query performance<br></font><br></div><div><div></div><div>
<div></div>Hi all,
<div><br></div>
<div>Another question on postgis query performance. I did some discussion on the 
pgsql performance list on how i could increase the performance on my 
server.</div>
<div>I did manage to increase the transactions per second, but came to the 
conclusion that this did not help the ST_Intersection query which i was trying 
to speed up.</div>
<div><br></div>
<div>Any suggestions on how to speed up this query? Basically i want to create 
an intersection between a vector grid and country shapefiles. Then calculate the 
area of all the polygons in the intersected table, and finally selecting the 
country code for each cell which represents the largest area within each cell. I 
does work as it is, but i would like to increase the speed. The query which 
takes a lot of time is the ST_Intersection.</div>
<div><br></div>
<div>"PostgreSQL 8.4.7 on i686-pc-linux-gnu, compiled by GCC gcc-4.4.real 
(Ubuntu/Linaro 4.4.4-14ubuntu5) 4.4.5, 32-bit"</div>
<div><br></div>
<div>"POSTGIS="2.0.0SVN" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 
2009" LIBXML="2.7.7" USE_STATS"</div>
<div><br></div>
<div><br></div>
<div>
<div>DROP TABLE IF EXISTS cshapesgrid1946;</div>
<div><br></div>
<div>SELECT ST_Intersection(priogrid_land.cell, cshapeswdate.geom) AS geom, 
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate, 
capname, caplong, caplat, col, row, xcoord, ycoord INTO cshapesgrid1946 FROM 
priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell, 
cshapeswdate.geom) AND cshapeswdate.gwsyear <=1946 AND cshapeswdate.gweyear 
>=1946 AND cshapeswdate.startdate <= '1946/1/1';</div>
<div><br></div>
<div>ALTER TABLE cshapesgrid1946 ADD COLUMN area float;</div>
<div><br></div>
<div>UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom);</div>
<div><br></div>
<div>DROP TABLE IF EXISTS pg1946;</div>
<div><br></div>
<div>SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT 
MAX(area) FROM cshapesgrid1946 b GROUP BY divider);</div>
<div><br></div>
<div>CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom);</div></div>
<div><br></div>
<div><br></div>
<div>Best,</div>
<div>Andreas</div></div></div></div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">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>
<br></blockquote></div><br></div>
</blockquote></div><br></div>