On Mon, Mar 07, 2011 at 10:49:48PM +0100, Andreas For Tollefsen wrote:
- The synchronous_commit off increased the TPS, but not the speed of the below
- query.
-
- Oleg:
- 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.
-
- priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode,
- ST_Intersection(pri
- ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land, cshapeswdate WHERE
- ST_In
- tersects(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)
Your estimated and actuals are way off, have you analyzed those tables?
Dave
