Hey, the type of info you should consider to give for a meaningfull answer : https://wiki.postgresql.org/wiki/Slow_Query_Questions
The slowness could come from many things. For instance layer.id = 339 AND ST_Intersects(layer.geom_4326, parcel.geom_4326); You use 2 conditions, postgres might choose to use first the bad one. You could try to manually force postgres to use first the spatial condition for instance. I'm guessing you have an index on layer.id anyway. Your query feels akward 2015-05-26 22:01 GMT+02:00 Alexander W. Rolek <a.ro...@gmail.com>: > 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
