[postgis-users] Slow ST_Intersects and Materialized Views

[Alexander W. Rolek]

I have two tables both which have a gemo_4326 columns with a GIST index.

   - gis.parcels (approximately 1 million records)
   - gis.layers (approximately 1 thousand records)

The gis.layers table has large multipolygons that can include thousands of
gis.parcel intersects. When I run ST_Intersects from a gis.parcel row to
gis.layers the query is pretty quick (sub 100 ms). When I run an
ST_Intersects on a large multipolygon from gis.layers to gis.parcels to
find which parcels intersect with the gis.layer, the queries can take
upwards of 10 minutes.

Here's my query:

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

Two questions:

   - How can I improve performance on the ST_Intersects from gis.layers ->
   gis.parcels?
   - I'm considering caching the results in a Materialized View, but based
   on the current performance this would take a couple days. Is there an
   alternative approach for caching the results?


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