[postgis-users] RE Slow ST_Intersects and Materialized Views
Steve.Toutant at inspq.qc.ca
Steve.Toutant at inspq.qc.ca
Wed May 27 05:22:05 PDT 2015
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>
More information about the postgis-users
mailing list