The best way to see how a query can be affected by an index is by running the query before and after the addition of an index. In this recipe, in order to avoid the need to define the schema, all the tables are assumed to be on the public schema. The following steps will guide you through the process of optimizing a query with an index:
- Run the following query, which returns the names of all the schools found in San Francisco:
SELECT schoolid FROM caschools sc JOIN sfpoly sf ON ST_Intersects(sf.geom, ST_Transform(sc.geom, 3310));
- The results from the query do not matter. We are more interested in the time it took to run the query. When we run the query three times, it runs with the following elapsed times; your numbers ...
