Improving queries

Once you understand what a query is doing, getting it to do something better can be a difficult road to walk down. There are some common techniques that mesh with the areas PostgreSQL is known to be good and bad at.

Note that your first steps to improve queries should be to check whether the statistics the optimizer is working with seem reasonable. If it's bad, you may fix the problem by rewriting the query you have today to improve things. But, you can expect that future queries will run into the same class of issue, so fixing that from a statistics standpoint is the preferred way, the one that's more likely to continue reflecting future trends in the data. But improving the statistics about your data only goes so far some days.

Optimizing for fully cached data sets

A basic assumption of the query optimizer is that data is not cached in memory, and therefore all access to an index or table might require some disk activity to retrieve. The planner parameters seq_page_cost...