[postgis-users] Slow ST_Intersects and Materialized Views

[Bborie Park]

Can you redo that EXPLAIN with EXPLAIN ANALYZE VERBOSE? That'll give use
what the database actually does...

On Thu, May 28, 2015 at 8:09 AM, Alexander W. Rolek <a.rolek at gmail.com>
wrote:

> I appreciate the quick responses. Here's the EXPLAIN for the following
> query:
>
> EXPLAIN
> 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);
>
> Output:
>
> "HashAggregate  (cost=543.84..805.23 rows=26139 width=11)"
> "  Group Key: parcel.apn"
> "  ->  Nested Loop  (cost=5.40..478.49 rows=26139 width=11)"
> "        ->  Index Scan using layers_pkey on layers layer
>  (cost=0.27..8.29 rows=1 width=56409)"
> "              Index Cond: (id = 339)"
> "        ->  Bitmap Heap Scan on parcels parcel  (cost=5.13..469.83
> rows=37 width=1018)"
> "              Recheck Cond: (layer.geom_4326 && geom_4326)"
> "              Filter: _st_intersects(layer.geom_4326, geom_4326)"
> "              ->  Bitmap Index Scan on parcels_geom_4326
>  (cost=0.00..5.12 rows=111 width=0)"
> "                    Index Cond: (layer.geom_4326 && geom_4326)"
>
>
> @Steve.Toutant, I tried your approach, but I'm still getting really slow
> queries(minutes). Any other ideas?
>
> Alexander 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/20150528/08f8df67/attachment.html>