Re: Optimise PostgreSQL for fast testing

On 23/02/2012, at 7:35 PM, Marti Raudsepp wrote:

> If you have lots of very simple queries, then usually much of the
> overhead is in query planning. There are a few tricks to dumb down the
> planner to make it faster -- although that may come at the cost of
> slowing down execution.
>
> * If your queries use joins at all, you can reduce planning overhead
> by setting join_collapse_limit=1 and from_collapse_limit=1 or some
> other low number.
> * Set default_statistics_target=5 or another low number and run
> ANALYZE on the whole database.
> * Set enable_bitmapscan=off, enable_material=off,
> enable_mergejoin=off, enable_hashjoin=off -- this will prevent the
> planner for trying certain kinds of plans in the first place.
>
> Just for the heck of it, you might gain a bit by setting
> track_activities=off, update_process_title=off

Thanks a lot Marti.
After I've applied these settings I couldn't see major improvement over fsync=off. It was pretty much within the error margin.

But you're right that most of the queries are simple and I hope this will work a little bit faster on small runs (like a single test suite).
Will definitely keep an eye on these settings.

So far fsync=off is the best I could get.

Thanks one more time for the help.

Cheers.