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