<div dir="ltr">Hi,<div><br></div><div>The planner considers a seq scan is faster when only one record is expected.</div><div>You can disable seq scan for this query and force the index usage by setting:</div><div><br></div>
<div>set enable_seqscan to off;</div><div><br></div><div>Nicolas</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On 29 July 2014 12:05, Alex Reitbort <span dir="ltr"><<a href="mailto:alex@skylinesoft.com" target="_blank">alex@skylinesoft.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div lang="EN-US" link="blue" vlink="purple"><div><p class="MsoNormal">Hello,<u></u><u></u></p><p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">I am using Postgre 9.3.4 with postgis 2.1.3<u></u><u></u></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"<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></p>
<p class="MsoNormal">Here is a my query:<u></u><u></u></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;<u></u><u></u></i></b></p>
<p class="MsoNormal"><u></u> <u></u></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. <u></u><u></u></p><p class="MsoNormal">This is the results of explain analyze<u></u><u></u></p>
<p class="MsoNormal"><i>"Bitmap Heap Scan on osm_buildings (cost=876627.31..4408250.75 rows=21800115 width=160)"<u></u><u></u></i></p><p class="MsoNormal"><i>" Recheck Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<u></u><u></u></i></p>
<p class="MsoNormal"><i>" -> Bitmap Index Scan on osm_buildings_index (cost=0.00..871177.29 rows=21800115 width=0)"<u></u><u></u></i></p><p class="MsoNormal"><i>" Index Cond: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<u></u><u></u></i></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></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.<u></u><u></u></p>
<p class="MsoNormal">This is the explain analyze for query with limit clause:<u></u><u></u></p><p class="MsoNormal"><i>"Limit (cost=0.00..0.21 rows=1 width=160)"<u></u><u></u></i></p><p class="MsoNormal"><i>" -> Seq Scan on osm_buildings (cost=0.00..4621629.20 rows=21800115 width=160)"<u></u><u></u></i></p>
<p class="MsoNormal"><i>" Filter: (way && '0103000020E6100000010000000500000000000000C07159C00000000000C1414000000000C07159C00000000080D7414000000000806659C00000000080D7414000000000806659C00000000000C1414000000000C07159C00000000000C14140'::geometry)"<u></u><u></u></i></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></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?<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><u></u> <u></u></p><p class="MsoNormal"><b><span style="font-family:"Bookman Old Style","serif";color:#1f497d">Alex Reitbort<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Bookman Old Style","serif";color:#1f497d">Software Developer<u></u><u></u></span></b></p><p class="MsoNormal"><b><span style="font-family:"Bookman Old Style","serif";color:#1f497d">Skyline Software Systems Inc.<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span style="font-family:"Bookman Old Style","serif""><a href="http://www.skylineglobe.com" target="_blank"><span style="color:blue">www.skylineglobe.com</span></a><u></u><u></u></span></b></p>
<p class="MsoNormal"><u></u> <u></u></p></div></div><br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>