<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:SimSun;
panose-1:2 1 6 0 3 1 1 1 1 1;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Bookman Old Style";
panose-1:2 5 6 4 5 5 5 2 2 4;}
@font-face
{font-family:"\@SimSun";
panose-1:2 1 6 0 3 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal>Hello,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I am using Postgre 9.3.4 with postgis 2.1.3<o:p></o:p></p><p class=MsoNormal> "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"<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>What I want to know if there are any rows in specified rectangular bbox. For this I tried using && spatial operator with limit clause.<o:p></o:p></p><p class=MsoNormal>Here is a my query:<o:p></o:p></p><p class=MsoNormal><b><i>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;<o:p></o:p></i></b></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>When I execute this query <b>without limit clause</b>, it uses the spatial index and returns me the results in 20ms. <o:p></o:p></p><p class=MsoNormal>This is the results of explain analyze<o:p></o:p></p><p class=MsoNormal><i>"Bitmap Heap Scan on osm_buildings (cost=876627.31..4408250.75 rows=21800115 width=160)"<o:p></o:p></i></p><p class=MsoNormal><i>" Recheck Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<o:p></o:p></i></p><p class=MsoNormal><i>" -> Bitmap Index Scan on osm_buildings_index (cost=0.00..871177.29 rows=21800115 width=0)"<o:p></o:p></i></p><p class=MsoNormal><i>" Index Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<o:p></o:p></i></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>However when I execute this query <b>with limit clause</b> it decides to do a full table scan without using the index and takes more than 15 seconds to finish.<o:p></o:p></p><p class=MsoNormal>This is the explain analyze for query with limit clause:<o:p></o:p></p><p class=MsoNormal><i>"Limit (cost=0.00..0.21 rows=1 width=160)"<o:p></o:p></i></p><p class=MsoNormal><i>" -> Seq Scan on osm_buildings (cost=0.00..4621629.20 rows=21800115 width=160)"<o:p></o:p></i></p><p class=MsoNormal><i>" Filter: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<o:p></o:p></i></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><b><span style='font-family:"Bookman Old Style","serif";color:#1F497D'>Alex Reitbort<o:p></o:p></span></b></p><p class=MsoNormal><b><span style='font-family:"Bookman Old Style","serif";color:#1F497D'>Software Developer<o:p></o:p></span></b></p><p class=MsoNormal><b><span style='font-family:"Bookman Old Style","serif";color:#1F497D'>Skyline Software Systems Inc.<o:p></o:p></span></b></p><p class=MsoNormal><b><span style='font-family:"Bookman Old Style","serif"'><a href="www.skylineglobe.com"><span style='color:blue'>www.skylineglobe.com</span></a><o:p></o:p></span></b></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>