Damn, send too early.
Your query feels awkward, if you want the link between layer and parcel, you should have at least 2 terms in the select. When you precise a little bit why your query is slow, it will be possible to propose solutions adapted. Cheers, Rémi-C 2015-05-27 10:19 GMT+02:00 Rémi Cura <remi.c...@gmail.com>: > 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