[postgis-users] Postgis intersect query with limit does not use spatial index

[Nicolas Ribot]

Hi,

The planner considers a seq scan is faster when only one record is expected.
You can disable seq scan for this query and force the index usage by
setting:

set enable_seqscan to off;

Nicolas


On 29 July 2014 12:05, Alex Reitbort <alex at skylinesoft.com> wrote:

> Hello,
>
>
>
> I am  using Postgre 9.3.4 with postgis 2.1.3
>
> "PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 64-bit
> POSTGIS="2.1.3 r12547" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6
> March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8"
> LIBJSON="UNKNOWN" RASTER"
>
>
>
> I have a spatial table with 100 000 000 rows. This table has two columns
> sgid (primary key) and way(geometry(MultiPolygon,4326)). There is a spatial
> index on the spatial column.
>
>
>
> What I want to know if there are any rows in specified rectangular bbox.
> For this I tried using && spatial operator with limit clause.
>
> Here is a my query:
>
> *select * from "public"."osm_buildings" where
> "public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
> 35.5078125,-101.6015625 35.68359375)'::box2d,4326) limit 1;*
>
>
>
> When I execute this query *without limit clause*, it uses the spatial
> index and returns me the results in 20ms.
>
> This is the results of explain analyze
>
> *"Bitmap Heap Scan on osm_buildings  (cost=876627.31..4408250.75
> rows=21800115 width=160)"*
>
> *"  Recheck Cond: (way &&
> '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"*
>
> *"  ->  Bitmap Index Scan on osm_buildings_index  (cost=0.00..871177.29
> rows=21800115 width=0)"*
>
> *"        Index Cond: (way &&
> '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"*
>
>
>
>
>
> However when I execute this query *with limit clause* it decides to do a
> full table scan without using the index and takes more than 15 seconds to
> finish.
>
> This is the explain analyze for query with limit clause:
>
> *"Limit  (cost=0.00..0.21 rows=1 width=160)"*
>
> *"  ->  Seq Scan on osm_buildings  (cost=0.00..4621629.20 rows=21800115
> width=160)"*
>
> *"        Filter: (way &&
> '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"*
>
>
>
>
>
> How can I force the query with limit to use the index? What query can I
> use to check if there is any data in required bbox?
>
>
>
>
>
> *Alex Reitbort*
>
> *Software Developer*
>
> *Skyline Software Systems Inc.*
>
> *www.skylineglobe.com <http://www.skylineglobe.com>*
>
>
>
> _______________________________________________
> 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/20140729/62fd02fd/attachment.html>