Speeding PostgreSQL (was: Re: [BioSQL-l] Error in load_ncbi_taxonomy.pl)

[Yves Bastide]

Aaron J Mackey wrote:
> Of course as soon as I sent this I saw what I was doing wrong (not
> wrapping large sets of inserts/updates in single transactions).  Things
> should be significantly better now, please let me know if there are other
> things I can do.

Be sure you have reasonable values for postgres' buffers, as the 
defaults are not. E. g.:

shared_buffers = 1000       # 8 KiB each
max_fsm_relations = 1000    # ~40 bytes each
max_fsm_pages = 10000       # ~6 bytes each
sort_mem = 32768            # KiB
vacuum_mem = 32768          # KiB
effective_cache_size = 4000 # > 8KiB each

(these are in fact quite random, but ought to be better than the default :)

Also VACUUM ANALYZE often, more so if you update/delete rows.

> 
> -Aaron
> 

yves