<div dir="ltr"><div>Hey,<br></div>it would be a very bad idea to use <br><div>
<div>set enable_seqscan to off;<br></div><div>for other things that analyzing planner behaviour.<br></div><div>(in perticular, but not limited to , if you do other think that this simple querry).<br></div><div><br></div>
<div>You may also want to check your postgres conf file regarding performances.<br></div><div>(and round a little bit this boudning box to limit the parsing work, maybe 3 digits after point would suffice?)<br></div><div><br>
</div><div>There must be a work around using CTE (I can't test it yet):<br><b><i>WITH found_geom AS (<br>select * from "public"."osm_buildings" where
"public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326)<br>)<br>SELECT *<br>FROM dounf_geom<br> limit 1;</i></b><br><br></div><div>However , why not simply try a "distinct" if your limit is always 1 :<br><b><i>SELECT DISTINCT ON (sgid) * <br>
FROM "public"."osm_buildings" <br>WHERE "public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326)</i></b> = TRUE<br></div><div>--optionnal order by to guarantee that same querry on same table return same things<br></div><div>ORDER BY sgid ASC; <br></div><div><br>
</div><div><br></div><div>Maybe you can also try with a subselect, but I doubt it will work :<br><b><i>SELECT *<br>FROM <br></i></b><b><i><b><i> (<br>select * from "public"."osm_buildings" where
"public"."osm_buildings"."way" && ST_SetSRID('BOX(-101.77734375
35.5078125,-101.6015625 35.68359375)'::box2d,4326)<br>) AS </i></b>dounf_geom<br> limit 1;</i></b><br><br><br></div><div>Cheers,<br>Rémi-C<br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">
2014-07-29 12:27 GMT+02:00 Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">By the way, did you analyse the table ?</div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On 29 July 2014 12:23, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><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"><div><div>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>
</div></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div link="blue" vlink="purple" lang="EN-US"><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></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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>
</blockquote></div><br></div>
</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>