[postgis-users] Increase query performance
Andreas Forø Tollefsen
andreasft at gmail.com
Tue Mar 8 09:01:57 PST 2011
After a suggestion from pgsql_performance i tried with ST_Simplify to speed
However this gives me a:
NOTICE: ptarray_simplify returned a <2 pts array
Then server connection terminates.
ST_Simplify(cshapeswdate.geom,0.1)) AS geom,
priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, enddate,
capname, caplong, caplat, col, row, xcoord, ycoord
FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell,
ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear <=1946 AND
cshapeswdate.gweyear >=1946 AND cshapeswdate.startdate <= '1946/1/1';
2011/3/8 Andreas Forø Tollefsen <andreasft at gmail.com>
> 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
> " Index Cond: (priogrid_land.cell && cshapeswdate.geom)"
> "Total runtime: 2524889.630 ms"
> 2011/3/8 Andreas Forø Tollefsen <andreasft at gmail.com>
>> 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
>> 2011/3/8 Paragon Corporation <lr at pcorp.us>
>>> 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,
>>> *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
>>> 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);
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users