Database Reference
In-Depth Information
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..4160.93 rows=4
width=9) (actual time=95.485..588.466
rows=234 loops=1)
Join Filter: ((sf.geom &&
st_transform(sc.geom, 3310)) AND
_st_intersects(sf.geom,
st_transform(sc.geom, 3310)))
Rows Removed by Join Filter: 13254
-> Seq Scan on sfpoly sf
(cost=0.00..1.01 rows=1 width=32) (actual
time=0.018..0.019 rows=1 loops=1)
-> Seq Scan on caschools sc
(cost=0.00..551.88 rows=13488 width=41)
(actual time=0.008..12.511 rows=13488
loops=1)
The
QUERY PLAN
tableisthesameasthatinstep4.Thequeryisnotusing
the spatial index. Why?
If you look at the query, we used
ST_Transform()
to reproject
caschools.geom
to the spatial reference system of
sfpoly.geom
. The
ST_Transform()
geometries used in the
ST_Intersects()
spatial test
wereinSRID3310,butthegeometriesusedforthe
caschools_geom_idx
index were in SRID 4269. This difference in spatial reference systems pre-
vented the use of the index in the query.
9. Wecancreateaspatialindexthatusesgeometriesprojectedinthedesired
spatialreferencesystem.Anindexthatusesafunctionisknownasa
func-
tional index
. It can be created as follows:
CREATE INDEX caschools_geom_3310_idx
ON caschools
USING gist
(ST_Transform(geom, 3310));
