Re: Optimise PostgreSQL for fast testing

On 02/23/2012 07:16 PM, Dmytrii Nagirniak wrote:
> That's totally fine if PG can't beat SQLite on speed in **this
> particular case**.
> I just want to try to tune it to be as fast as it can (for **this
> particular case**, see my reply to Adrian).

You can find all of the big tunable parameters at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server That's as
good of a general "how do I make this faster by tweaking the server"
guide as you'll get.

Once you've hit the big tunables--shared_buffers, checkpoint_segments,
work_mem, effective_cache_size, and tweaking either synchronous_commit
or fsync--there's not too much else you can do except dig into what's
slow in individual queries. Only other thing that might help is running
ANALYZE against the whole database after any major loading of test data,
just to make sure the queries are being executed with good statistics.

If you can extract the SQL from the test cases so they can be executed
directly with the psql client, you could add "\timing" before them to
see how long each individual query runs, to look for the long running
ones. It's possible that every statement is a little slower, which
would be unsurprising and not something you can really resolve if so.
It could just be a small number that are being executed poorly though,
in which case specific query tweaking might be possible. You might get
further insight by posting the EXPLAIN ANALYZE plans of whatever the
slowest single query is. More on that subject at
http://wiki.postgresql.org/wiki/Slow_Query_Questions

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com