Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I am not an expert at all in this stuff.

I've noticed that the manual mentions a few times that a good way to improve performance is to use better cost constants for the query planner, but it also says that there is no simple way to figure out what cost constants to use.

I think that the constants might be problematic right now, since estimated costs don't seem to be a steady multiple of actual execution time - it varies from 30x to about 600x, even after running VACUUM ANALYZE recently. (I don't know whether this is a valid way to check try to see if the constants are set well, correct me if I'm wrong on that)

So, I'd like to set better constants for the query planner. How do I go about doing this? Should I just tweak things up and down at random until it seems faster, or is there anything I should do that's more formal? Are there any guidelines based on hardware or anything?

By the way, if the answer is "Don't worry about it, other things will be way more important to improve first," that's fine - for my actual real-world case, I'll work on other stuff. But for the sake of others, it would still be good to know how one would improve the constants if everything else has been improved.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

First, the costs are not supposed to directly relate to the execution time. They're strictly relative; a plan which costs more should take longer to actually execute. You can adjust sequential_page_cost in order to "tune" costs so that they're closer to milleseconds of execution, but IMHO that's a waste of time.

For 99% of users, there's only three cost constants they care about at all:

effective_cache_size: set this to 75% of available RAM on your system. Done.

random_page_cost: if you're using high-end SAN/DAS, lower this to 2.0. If you're using SSDs or on Amazon, lower it to 1.5. Otherwise, leave it alone.

effective_io_concurrency: if you're on Linux and have a drive array, raise this to the number of drives (or mirrored pairs) the database is stored on, maximum 4.

Tinkering with the other cost constants is not recommended unless you have a full performance test setup to determine if the result is really beneficial or not.

share|improve this answer
    
Thanks, very helpful. What does a "full performance test setup" look like? –  kharybdis Aug 3 '12 at 20:16
1  
A server which is very similar to production, and a synthetic test workload based on production which you can run and do statistical measurements of comparative overall execution times from. The problem with just changing a parameter is it can make one query better while making many others worse. –  Josh Berkus Aug 14 '12 at 6:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.