Database Reference
In-Depth Information
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.016..0.017 rows=1 loops=1)
-> Seq Scan on caschools sc
(cost=0.00..551.88 rows=13488 width=41)
(actual time=0.009..13.671 rows=13488
loops=1)
5. What is significant in the preceding
QUERY PLAN
is
Join Filter
, which
hasconsumedmostoftheexecutiontime.Thismaybehappeningbecause
the
caschools
table does not have a spatial index on the
geom
column.
6. Add a spatialindex to the
geom
column, as follows:
CREATE INDEX caschools_geom_idx
ON caschools
USING gist
(geom);
7. Rerun the query from step 1 three times so as to minimize one-time anom-
alies. With a spatial index, the query ran with the following elapsed query
times:
Time: 451.231 ms
Time: 490.250 ms
Time: 469.842 ms
The query did not run much faster with the spatial index. What happened?
We need to check the
QUERY PLAN
.
8. Seeifthe
QUERY PLAN
changedinPostgreSQLusing
EXPLAIN ANALYZE
as follows:
