[postgis-users] Slow ST_Intersects and Materialized Views

[Alexander W. Rolek]

I appreciate the quick responses. Here's the EXPLAIN for the following
query:

EXPLAIN
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);

Output:

"HashAggregate  (cost=543.84..805.23 rows=26139 width=11)"
"  Group Key: parcel.apn"
"  ->  Nested Loop  (cost=5.40..478.49 rows=26139 width=11)"
"        ->  Index Scan using layers_pkey on layers layer  (cost=0.27..8.29
rows=1 width=56409)"
"              Index Cond: (id = 339)"
"        ->  Bitmap Heap Scan on parcels parcel  (cost=5.13..469.83 rows=37
width=1018)"
"              Recheck Cond: (layer.geom_4326 && geom_4326)"
"              Filter: _st_intersects(layer.geom_4326, geom_4326)"
"              ->  Bitmap Index Scan on parcels_geom_4326  (cost=0.00..5.12
rows=111 width=0)"
"                    Index Cond: (layer.geom_4326 && geom_4326)"


@Steve.Toutant, I tried your approach, but I'm still getting really slow
queries(minutes). Any other ideas?

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