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.ro...@gmail.com>@lists.osgeo.org Envoyé par : postgis-users-boun...@lists.osgeo.org 2015-05-26 16:01 Veuillez répondre à PostGIS Users Discussion <postgis-users@lists.osgeo.org> A PostGIS Users Discussion <postgis-users@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@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
