Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D97201337BDD for ; Mon, 7 Mar 2011 19:29:09 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 01526-04 for ; Mon, 7 Mar 2011 23:29:02 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from vexbert.mr-paradox.net (vexbert.mr-paradox.net [198.145.180.174]) by mail.postgresql.org (Postfix) with ESMTP id 8B4151337B4E for ; Mon, 7 Mar 2011 19:29:02 -0400 (AST) Received: by vexbert.mr-paradox.net (Postfix, from userid 18391) id 10E70846B4; Mon, 7 Mar 2011 18:29:01 -0500 (EST) Date: Mon, 7 Mar 2011 15:29:01 -0800 From: David Kerr To: Andreas =?iso-8859-1?Q?For=F8?= Tollefsen Cc: Oleg Bartunov , Kenneth Marshall , pgsql-performance@postgresql.org Subject: Re: Performance issues Message-ID: <20110307232901.GA35096@mr-paradox.net> References: <20110307140101.GL8169@aart.is.rice.edu> <20110307142216.GM8169@aart.is.rice.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.3i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/81 X-Sequence-Number: 42760 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