PostGIS Cookbook - Second Edition

How it works...

In this recipe, we optimized a query that users may commonly encounter while using PostGIS. We started by taking advantage of the PostgreSQL capabilities to improve the performance and syntax of our query. When performance could no longer improve, we ran EXPLAIN ANALYZE VERBOSE to find out what was consuming most of the query-execution time. We learned that the ST_Distance() function consumed the most time from the execution plan. We finally used the <-> operator of PostgreSQL 9.1 to dramatically improve the query-execution time to under a second.

The output of EXPLAIN ANALYZE VERBOSE used in this recipe is not easy to understand. For complex queries, it is recommended that you use the visual output in pgAdmin (discussed in ...