Optimizing SQL queries
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...
