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

[Nicolas Ribot]

By the way, did you analyse the table ?


On 29 July 2014 12:23, Nicolas Ribot <nicolas.ribot at gmail.com> wrote:

> 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/7e2c7ead/attachment.html>