[postgis-users] Slow ST_Intersects and Materialized Views (EXPLAIN ANALYZE VERBOSE)

[Paul Ramsey]

CREATE INDEX parcels_gix ON gis.parcels USING GIST (geom_4326);
ANALYZE;

On Thu, May 28, 2015 at 10:58 AM, Alexander W. Rolek <a.rolek at gmail.com> wrote:
> Bborie Park -
>
> Here's the expanded query:
>
> EXPLAIN ANALYZE VERBOSE
> 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);
>
> and the output:
>
> "HashAggregate  (cost=543.84..805.23 rows=26139 width=11) (actual
> time=465830.953..465835.537 rows=11967 loops=1)"
> "  Output: parcel.apn"
> "  Group Key: parcel.apn"
> "  ->  Nested Loop  (cost=5.40..478.49 rows=26139 width=11) (actual
> time=1326.282..465782.372 rows=14707 loops=1)"
> "        Output: parcel.apn"
> "        ->  Index Scan using layers_pkey on gis.layers layer
> (cost=0.27..8.29 rows=1 width=56409) (actual time=0.215..0.218 rows=1
> loops=1)"
> "              Output: layer.id, layer.type, layer.source, layer.data,
> layer.wiki, layer.geom_4326, layer.created, layer.updated"
> "              Index Cond: (layer.id = 339)"
> "        ->  Bitmap Heap Scan on gis.parcels parcel  (cost=5.13..469.83
> rows=37 width=1018) (actual time=1326.060..465748.934 rows=14707 loops=1)"
> "              Output: parcel.id, parcel.apn, parcel.source, parcel.data,
> parcel.wiki, parcel.geom_4326, parcel.created, parcel.updated"
> "              Recheck Cond: (layer.geom_4326 && parcel.geom_4326)"
> "              Rows Removed by Index Recheck: 116269"
> "              Filter: _st_intersects(layer.geom_4326, parcel.geom_4326)"
> "              Rows Removed by Filter: 901418"
> "              Heap Blocks: exact=34190 lossy=132122"
> "              ->  Bitmap Index Scan on parcels_geom_4326  (cost=0.00..5.12
> rows=111 width=0) (actual time=1278.238..1278.238 rows=916125 loops=1)"
> "                    Index Cond: (layer.geom_4326 && parcel.geom_4326)"
> "Planning time: 23.746 ms"
> "Execution time: 465846.118 ms"
>
>
> --
> Alexander W. Rolek
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users