Re: Performance issues

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(at)sai(dot)msu(dot)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(at)rice(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>