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

[Alex Reitbort]

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 &&
'0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C
07159C00000000080D7414000000000806659C00000000080D7414000000000806659C000000
00000C1414000000000C07159C00000000000C14140'::geometry)"

"  ->  Bitmap Index Scan on osm_buildings_index  (cost=0.00..871177.29
rows=21800115 width=0)"

"        Index Cond: (way &&
'0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C
07159C00000000080D7414000000000806659C00000000080D7414000000000806659C000000
00000C1414000000000C07159C00000000000C14140'::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 &&
'0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C
07159C00000000080D7414000000000806659C00000000080D7414000000000806659C000000
00000C1414000000000C07159C00000000000C14140'::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

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140729/8c4717de/attachment.html>