Database Reference
In-Depth Information
10. Rerun the query from step 1 three times to get the following output :
Time: 279.548 ms
Time: 263.896 ms
Time: 238.668 ms
That's better! From about 500 ms to 260 ms.
11. Check the
QUERY PLAN
table as follows:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..9.55 rows=4
width=9) (actual time=92.553..272.146
rows=234 loops=1)
-> Seq Scan on sfpoly sf
(cost=0.00..1.01 rows=1 width=32) (actual
time=0.013..0.016 rows=1 loops=1)
-> Index Scan using
caschools_geom_3310_idx on caschools sc
(cost=0.00..8.53 rows=1 width=41) (actual
time=91.762..270.965 rows=234 loops=1)
Index Cond: (sf.geom &&
st_transform(geom, 3310))Filter:
_st_intersects(sf.geom,
st_transform(geom, 3310))
Rows Removed by Filter: 34
12. The plan shows that the query used the
caschools_geom_3310_idx
in-
dex.The
Index Scan
commandwassignificantlyfasterthanthepreviously
used
Join Filter
command.
How it works...
Databaseindexeshelpusquicklyandefficientlyfindthevaluesweareinterestedin.
Generally,aqueryusinganindexisfasterthanonethatisnot,buttheperformance
improvement may not be to the degree found in this recipe.
