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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
