[postgis-users] RE Slow ST_Intersects and Materialized Views

[Steve.Toutant at inspq.qc.ca]

Alexander,
Using explain, make sure the where clause is used before the intersects 
function. 
Maybe this query will help.
SELECT DISTINCT parcel.apn
FROM 
gis.parcels as parcel
JOIN (select geom_4326 from gis.layers where layer.id = 339) as layer ON 
ST_Intersects(layer.geom_4326, parcel.geom_4326); 

It might help to have an index on layer.id...maybe not...you coulkd try





"Alexander W. Rolek" <a.rolek at gmail.com>@lists.osgeo.org 
Envoyé par : postgis-users-bounces at lists.osgeo.org
2015-05-26 16:01
Veuillez répondre à
PostGIS Users Discussion <postgis-users at lists.osgeo.org>


A
PostGIS Users Discussion <postgis-users at lists.osgeo.org>
cc

Objet
[postgis-users] Slow ST_Intersects and Materialized Views








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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150527/19b26078/attachment.html>