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@xxxxxxxxxxxxxxx Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
