[postgis-users] Slow ST_Intersects and Materialized Views

[Rémi Cura]

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.rolek at 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 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/b288333c/attachment.html>