Database Reference
In-Depth Information
Optimizing SQL queries
WhenaSQLqueryisreceived,PostgreSQLrunsthequerythroughitsplannertode-
cidethebestexecutionplan.Thebestexecutionplangenerallyresultsinthefastest
queryperformance.Thoughtheplannerusuallymakesthecorrectchoices,onocca-
sion, a specific query will have a suboptimal execution plan.
Forthesesituations,thefollowingareseveralthingsthatcanbedonetochangethe
behavior of the PostgreSQL planner:
• Add appropriate column indexesto 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. Up-
dating statistics (item 2) is generally done automatically by PostgreSQL after a cer-
tain amount of table activity. But, the statistics can be manually updated using the
ANALYZE
statement.Changingthedatabaselayoutandthequeryplanner'sconfigur-
ation(items4and5,respectively)areadvancedoperationsusedonlywhenthefirst
three items have already been attempted and thus, will not be discussed further.
This recipe only discusses item 3, that is, optimizing performance by rewriting SQL
queries.
Getting ready
Forthisrecipe,wewillfindthenearestpolicestationtoeveryschoolandthedistance
in meters between each school in San Francisco and its nearest station, as fast as
possible.Thiswillrequireustorewriteourquerymanytimestobemoreefficientand
take advantage of new PostgreSQL capabilities.
