Forgot to mention that the query terminates the connection because of a crash of server process.
2011/3/8 Andreas Forø Tollefsen <andrea...@gmail.com> > Andy. Thanks. That is a great tips. I tried it but i get the error: > NOTICE: ptarray_simplify returned a <2 pts array. > > Query: > SELECT ST_Intersection(priogrid_land.cell, > 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 Andy Colson <a...@squeakycode.net> > > I have seen really complex geometries cause problems. If you have >> thousands of points, when 10 would do, try ST_Simplify and see if it doesnt >> speed things up. >> >> -Andy >> >> >> >> On 3/8/2011 2:42 AM, Andreas Forř Tollefsen wrote: >> >>> Hi. Thanks for the comments. My data is right, and the result is exactly >>> what i want, but as you say i think what causes the query to be slow is >>> the ST_Intersection which creates the intersection between the vector >>> grid (fishnet) and the country polygons. >>> I will check with the postgis user list if they have any idea on how to >>> speed up this query. >>> >>> Best, >>> Andreas >>> >>> 2011/3/8 Tom Lane <t...@sss.pgh.pa.us <mailto:t...@sss.pgh.pa.us>> >>> >>> >>> =?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?= <andrea...@gmail.com >>> <mailto:andrea...@gmail.com>> writes: >>> > This is a query i am working on now. It creates an intersection >>> of two >>> > geometries. One is a grid of 0.5 x 0.5 decimal degree sized >>> cells, while the >>> > other is the country geometries of all countries in the world for >>> a certain >>> > year. >>> >>> Hm, are you sure your data is right? Because the actual rowcounts >>> imply >>> that each country intersects about half of the grid cells, which >>> doesn't >>> seem right. >>> >>> > priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode, >>> > ST_Intersection(pri >>> > ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land, >>> cshapeswdate WHERE >>> > ST_Intersects(priogrid_land.cell, cshapeswdate.geom); >>> > >>> QUERY >>> > PLAN >>> >>> > >>> >>> >>> -------------------------------------------------------------------------------- >>> > ------------------------------------------------------------------ >>> > Nested Loop (cost=0.00..12644.85 rows=43351 width=87704) (actual >>> > time=1.815..7 >>> > 074973.711 rows=130331 loops=1) >>> > Join Filter: _st_intersects(priogrid_land.cell, >>> cshapeswdate.geom) >>> > -> Seq Scan on cshapeswdate (cost=0.00..14.42 rows=242 >>> width=87248) >>> > (actual >>> > time=0.007..0.570 rows=242 loops=1) >>> > -> Index Scan using idx_priogrid_land_cell on priogrid_land >>> > (cost=0.00..7.1 >>> > 5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242) >>> > Index Cond: (priogrid_land.cell && cshapeswdate.geom) >>> > Total runtime: 7075188.549 ms >>> > (6 rows) >>> >>> AFAICT, all of the runtime is going into calculating the ST_Intersects >>> and/or ST_Intersection functions. The two scans are only accounting >>> for >>> perhaps 5.5 seconds, and the join infrastructure isn't going to be >>> terribly expensive, so it's got to be those functions. Not knowing >>> much >>> about PostGIS, I don't know if the functions themselves can be >>> expected >>> to be really slow. If it's not them, it could be the cost of fetching >>> their arguments --- in particular, I bet the country outlines are very >>> large objects and are toasted out-of-line. There's been some past >>> discussion of automatically avoiding repeated detoastings in scenarios >>> like the above, but nothing's gotten to the point of acceptance yet. >>> Possibly you could do something to force detoasting in a subquery. >>> >>> regards, tom lane >>> >>> >>> >> >
