From: David Kerr <dmk@mr-paradox.net>
To: Andreas Forø Tollefsen <andreasft@gmail.com>
Cc: Oleg Bartunov <oleg@sai.msu.su>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Mon, 7 Mar 2011 15:29:01 -0800
Message-ID: <20110307232901.GA35096@mr-paradox.net> (raw)
In-Reply-To: <AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com>
References: <AANLkTinZkVapdjrZULpc8zmwb4w8wotkYzj0gqj_jPXb@mail.gmail.com>
<20110307140101.GL8169@aart.is.rice.edu>
<AANLkTim71gwPa3y0Sggdzqh+-m=DY7-NWNe-BE7XwwPb@mail.gmail.com>
<20110307142216.GM8169@aart.is.rice.edu>
<AANLkTim4RoKaQcBWhjer34a0EjSX-PWQkoXGYV0YY=kM@mail.gmail.com>
<Pine.LNX.4.64.1103071834040.14517@sn.sai.msu.ru>
<AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dmk@mr-paradox.net, andreasft@gmail.com, oleg@sai.msu.su, ktm@rice.edu
Subject: Re: Performance issues
In-Reply-To: <20110307232901.GA35096@mr-paradox.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox