Database Reference
In-Depth Information
10. Inthe
EXPLAIN ANALYZE VERBOSE
output,wewanttoinspectthevalues
for the actual time, which provide the actual start and end times for that
part of the query. Of all the actual time ranges, the value actual time,
174.856..3858.436,forthe
Nested Loop
(highlightedintheprecedingout-
put) is the worst. This query step consumes at least 80 percent of the total
executiontime,soanyworkdonetoimproveperformancemustbedonein
this step.
11. The columns returned from the slow
Nested Loop
utility is found in the
value for the output. Of these columns,
st_distance()
is present only in
thisstepandnotinanyinnerstep.Thismeanswewillneedtomitigatethe
number of calls to
ST_Distance()
.
12. At this step, further query improvements are not possible without running
PostgreSQL 9.1 or a higher version. PostgreSQL 9.1 introduced indexed
nearest-neighborsearchesusingthe
<->
and
<#>
operatorstocomparethe
geometries' convex hulls and bounding boxes, respectively. For point geo-
metries, both operators result in the same answer.
13. Let'srewritethequerytotakeadvantageofthe
<->
operator.Thefollowing
query still uses the CTEs andwindow functions:
WITH sc AS ( -- get schools in San
Francisco
SELECT
ca.gid,
ca.name,
ca.geom
FROM sfpoly sf
JOIN caschools ca
ON ST_Intersects(sf.geom,
ST_Transform(ca.geom, 3310))
), po AS ( -- get police stations in San
Francisco
SELECT
ca.gid,
ca.address,
ca.geom
FROM sfpoly sf
JOIN capolice ca
ON ST_Intersects(sf.geom,
