When an 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 indices 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 the layout of the database tables
• Change the query planner's configuration

Adding indices (the first bullet point) is discussed in a separate recipe found in this chapter. Updating statistics (the second point) is generally done automatically by PostgreSQL after a certain amount of table activity, but the statistics can be manually updated using the ANALYZE statement. Changing the database layout and the query planner's configuration (the fourth and fifth bullet point, respectively) are advanced operations used only when the first three points have already been attempted and, thus, will not be discussed further.

This recipe only discusses the third option - that is, optimizing performance by rewriting SQL queries.