[postgis-users] Increase query performance

[Andreas Forø Tollefsen]

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 <andreasft at 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 <lr at 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-bounces at postgis.refractions.net [mailto:
>> postgis-users-bounces at 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 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/20110308/cd16f74c/attachment.html>