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