Optimizing SQL queries

When a SQL query is received, PostgreSQL runs the query through its planner to decide the best execution plan. The best execution plan generally results in the fastest query performance. Though the planner usually makes the correct choices, on occasion, a specific query will have a suboptimal execution plan.

For these situations, the following are several things that can be done to change the behavior of the PostgreSQL planner:

• Add appropriate column indexes to the tables in question

• Update the statistics of the database tables

• Rewrite the SQL query by evaluating the query's execution plan and using capabilities available in your PostgreSQL installation

• Consider changing or adding to the layout of the database tables

• Change the query planner's configuration

Adding indexes (item 1) is discussed in a separate recipe found in this chapter. Updating statistics (item 2) is generally done automatically by PostgreSQL after a certain amount of table activity. But, the statistics...