Re: Performance issues

Поиск
Список
Период
Сортировка
От Andreas Forø Tollefsen
Тема Re: Performance issues
Дата
Msg-id AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Performance issues  (Oleg Bartunov <oleg@sai.msu.su>)
Ответы Re: Performance issues
Re: Performance issues
Список pgsql-performance
Дерево обсуждения
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)

2011/3/7 Oleg Bartunov <oleg@sai.msu.su>
On Mon, 7 Mar 2011, Andreas For? Tollefsen wrote:

Ok. Cheers. I will do some more testing on my heavy PostGIS queries which
often takes hours to complete.

I'd like to see hours long queries :) EXPLAIN ANALYZE



Thanks.
Andreas

2011/3/7 Kenneth Marshall <ktm@rice.edu>

On Mon, Mar 07, 2011 at 03:17:05PM +0100, Andreas For? Tollefsen wrote:
Thanks, Ken.

It seems like the tip to turn off synchronous_commit did the trick:

/usr/lib/postgresql/8.4/bin/pgbench -T 60 test1
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
duration: 60 s
number of transactions actually processed: 86048
tps = 1434.123199 (including connections establishing)
tps = 1434.183362 (excluding connections establishing)

Is this acceptable compared to others when considering my setup?

Cheers,
Andreas



These are typical results for synchronous_commit off. The caveat
is you must be able to handle loosing transactions if you have a
database crash, but your database is still intact. This differs
from turning fsync off in which a crash means you would need to
restore from a backup.

Cheers,
Ken



       Regards,
               Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

В списке pgsql-performance по дате отправления:

Предыдущее
От: Merlin Moncure
Дата:
Сообщение: Re: Query performance with disabled hashjoin and mergejoin
Следующее
От: David Kerr
Дата:
Сообщение: Re: Performance issues