[postgis-users] Slow ST_Intersects and Materialized Views (EXPLAIN ANALYZE VERBOSE)

[Alexander W. Rolek]

Bborie Park -

Here's the expanded query:

EXPLAIN ANALYZE VERBOSE
SELECT DISTINCT
parcel.apn
FROM
gis.parcels as parcel,
gis.layers as layer
WHERE
layer.id = 339 AND
ST_Intersects(layer.geom_4326, parcel.geom_4326);

and the output:

"HashAggregate  (cost=543.84..805.23 rows=26139 width=11) (actual
time=465830.953..465835.537 rows=11967 loops=1)"
"  Output: parcel.apn"
"  Group Key: parcel.apn"
"  ->  Nested Loop  (cost=5.40..478.49 rows=26139 width=11) (actual
time=1326.282..465782.372 rows=14707 loops=1)"
"        Output: parcel.apn"
"        ->  Index Scan using layers_pkey on gis.layers layer
 (cost=0.27..8.29 rows=1 width=56409) (actual time=0.215..0.218 rows=1
loops=1)"
"              Output: layer.id, layer.type, layer.source, layer.data,
layer.wiki, layer.geom_4326, layer.created, layer.updated"
"              Index Cond: (layer.id = 339)"
"        ->  Bitmap Heap Scan on gis.parcels parcel  (cost=5.13..469.83
rows=37 width=1018) (actual time=1326.060..465748.934 rows=14707 loops=1)"
"              Output: parcel.id, parcel.apn, parcel.source, parcel.data,
parcel.wiki, parcel.geom_4326, parcel.created, parcel.updated"
"              Recheck Cond: (layer.geom_4326 && parcel.geom_4326)"
"              Rows Removed by Index Recheck: 116269"
"              Filter: _st_intersects(layer.geom_4326, parcel.geom_4326)"
"              Rows Removed by Filter: 901418"
"              Heap Blocks: exact=34190 lossy=132122"
"              ->  Bitmap Index Scan on parcels_geom_4326  (cost=0.00..5.12
rows=111 width=0) (actual time=1278.238..1278.238 rows=916125 loops=1)"
"                    Index Cond: (layer.geom_4326 && parcel.geom_4326)"
"Planning time: 23.746 ms"
"Execution time: 465846.118 ms"


-- 
Alexander W. Rolek
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150528/2c63e758/attachment.html>