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@xxxxxxxxxx>
On Mon, 7 Mar 2011, Andreas For? Tollefsen wrote:I'd like to see hours long queries :) EXPLAIN ANALYZE
Ok. Cheers. I will do some more testing on my heavy PostGIS queries which
often takes hours to complete.
Regards,
Thanks.
Andreas
2011/3/7 Kenneth Marshall <ktm@xxxxxxxx>
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
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
