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

[Rémi Cura]

Hey,
it would be a very bad idea to use
set enable_seqscan to off;
for other things that analyzing planner behaviour.
(in perticular, but not limited to , if you do other think that this simple
querry).

You may also want to check your postgres conf file regarding performances.
(and round a little bit this boudning box to limit the parsing work, maybe
3 digits after point would suffice?)

There must be a work around using CTE (I can't test it yet):





*WITH found_geom AS (select * from "public"."osm_buildings" where
"public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326))SELECT *FROM
dounf_geom limit 1;*

However , why not simply try a "distinct" if your limit is always 1 :


*SELECT DISTINCT ON (sgid) * FROM "public"."osm_buildings" WHERE
"public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326)* = TRUE
--optionnal order by to guarantee that same querry on same table return
same things
ORDER BY sgid ASC;


Maybe you can also try with a subselect, but I doubt it will work :


*SELECT *FROM *


* (select * from "public"."osm_buildings" where
"public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326)) AS dounf_geom limit 1;*


Cheers,
Rémi-C


2014-07-29 12:27 GMT+02:00 Nicolas Ribot <nicolas.ribot at gmail.com>:

> 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
>>>
>>
>>
>
> _______________________________________________
> 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/561199eb/attachment.html>