Re: Performance issues

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 <tgl(at)sss(dot)pgh(dot)pa(dot)us>

> =?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?= <andreasft(at)gmail(dot)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
>