[postgis-users] Slow ST_Intersects and Materialized Views

[Rémi Cura]

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.cura at 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.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/c39691ce/attachment.html>