Results from the EXPLAIN ANALYZE: "Nested Loop (cost=0.00..1189.72 rows=3941 width=87790) (actual time=7.091..2524830.264 rows=54145 loops=1)" " Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)" " -> Seq Scan on cshapeswdate (cost=0.00..16.23 rows=22 width=87304) (actual time=0.011..0.542 rows=72 loops=1)" " Filter: ((gwsyear <= 1946::numeric) AND (gweyear >= 1946::numeric) AND (startdate <= '1946-01-01'::date))" " -> 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)" " Index Cond: (priogrid_land.cell && cshapeswdate.geom)" "Total runtime: 2524889.630 ms"
2011/3/8 Andreas Forø Tollefsen <andrea...@gmail.com> > Hi, > This query takes about 41 minutes per year. Doing this for every year from > 1946 to 2008 takes a lot of time. > The grid consists of 64818 cell polygons, while the country table has about > 210 polygons. > > I will add indexes on year and date and try again. An do an explain > analyze. > > Cheers, > Andreas > > 2011/3/8 Paragon Corporation <l...@pcorp.us> > >> Andreas, >> 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? >> 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. >> >> Do you have indexes on your year fields and date columns? >> >> Hope that helps, >> Regina >> http://www.postgis.us >> >> >> ------------------------------ >> *From:* postgis-users-boun...@postgis.refractions.net [mailto: >> postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Andreas >> Forø Tollefsen >> *Sent:* Tuesday, March 08, 2011 4:15 AM >> *To:* PostGIS Users Discussion >> *Subject:* [postgis-users] Increase query performance >> >> Hi all, >> >> 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. >> 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. >> >> 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. >> >> "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" >> >> "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" >> >> >> DROP TABLE IF EXISTS cshapesgrid1946; >> >> 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'; >> >> ALTER TABLE cshapesgrid1946 ADD COLUMN area float; >> >> UPDATE cshapesgrid1946 SET area = ST_Area(cshapesgrid1946.geom); >> >> DROP TABLE IF EXISTS pg1946; >> >> SELECT * INTO pg1946 FROM cshapesgrid1946 a WHERE a.area IN (SELECT >> MAX(area) FROM cshapesgrid1946 b GROUP BY divider); >> >> CREATE INDEX idx_pg1946 ON pg1946 USING gist(geom); >> >> >> Best, >> Andreas >> >> _______________________________________________ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> >
_______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
