PostGIS Cookbook - Second Edition

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 ...