Re: Optimise PostgreSQL for fast testing

On Thu, Feb 23, 2012 at 08:02, Dmytrii Nagirniak <dnagir(at)gmail(dot)com> wrote:
> Thanks. So far I tried:
>
> fsync = off
> full_page_writes = off
>
> It seems it got a *little* faster (down to ~65 seconds from ~76) but is till
> too far from my target of ~34 secs.

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

Regards,
Marti