<div dir="ltr">Superthank you!<div style>It works and very fast!</div><div style>Bie</div><div style>Pietro</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">2013/1/22 Nicolas Ribot-2 [via PostGIS] <span dir="ltr"><<a href="/user/SendEmail.jtp?type=node&node=5002380&i=0" target="_top" rel="nofollow" link="external">[hidden email]</a>></span><br>
<blockquote style='border-left:2px solid #CCCCCC;padding:0 1em' class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><div class="im">Hi,<div><br></div><div>GIST index is only used with GIST operators: </div><div>&&, st_intersects, st_touches, etc.</div><div><br></div><div>You should rewrite your query to define a geographic bounding box and test your data against it:</div>
<div><br></div></div><div><div class="im"><span style="font-family:arial,sans-serif;font-size:13px">SELECT the_geom, layer, id</span><br style="font-family:arial,sans-serif;font-size:13px"><span style="font-family:arial,sans-serif;font-size:13px"> FROM gauss_b.polilineegb</span><br style="font-family:arial,sans-serif;font-size:13px">
</div><div class="im"><span style="font-family:arial,sans-serif;font-size:13px"> where the_geom && 'BOX(</span><span style="font-family:arial,sans-serif;font-size:13px">2365385 </span><span style="font-family:arial,sans-serif;font-size:13px">5075215, </span><span style="font-family:arial,sans-serif;font-size:13px">2366019 </span><span style="font-family:arial,sans-serif;font-size:13px">5075752</span><span style="font-family:arial,sans-serif;font-size:13px">)'::box2d;</span></div>
</div>
<div><br></div><div><span style="font-family:arial,sans-serif;font-size:13px">Nicolas</span></div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div class="h5">On 22 January 2013 15:38, Pietro Rossin <span dir="ltr"><<a href="http://user/SendEmail.jtp?type=node&node=5002378&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span> wrote:<br>
</div></div><blockquote style='border-left:2px solid #CCCCCC;padding:0 1em' style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div><div class="h5">Hello<br>
I have a table with about 6.000.000 polilines records.<br>
The geometry value is in the_geom column.<br>
I made an index with this instruction<br>
<br>
*************<br>
CREATE INDEX idx_polilineegb_geom<br>
ON gauss_b.polilineegb<br>
USING gist<br>
(the_geom );<br>
*************<br>
<br>
If I try a query like this:<br>
**********************<br>
SELECT the_geom, layer, id<br>
FROM gauss_b.polilineegb<br>
where<br>
(st_xmin(the_geom)=2365385 and st_ymin(the_geom)=5075215) and<br>
(st_xmax(the_geom)=2366019 and st_ymax(the_geom)=5075752);<br>
**********************<br>
<br>
It takes ages to execute..<br>
I take a look at the explain query it says:<br>
<br>
************************<br>
"Seq Scan on polilineegb (cost=0.00..727911.08 rows=1 width=2924)"<br>
" Filter: ((st_xmin((the_geom)::box3d) = 2365385::double precision) AND<br>
(st_ymin((the_geom)::box3d) = 5075215::double precision) AND<br>
(st_xmax((the_geom)::box3d) = 2366019::double precision) AND<br>
(st_ymax((the_geom)::box3d) = 5075752::double precision))"<br>
************************<br>
This is a Seq Scan, so it doesn't use the index, right?<br>
Why?<br>
Is there some more performing index to be used??<br>
<br>
Thank you<br>
Pietro<br>
<br>
<br>
<br>
<br>
<br>
--<br>
View this message in context: <a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html" rel="nofollow" link="external" target="_blank">http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html</a><br>
Sent from the PostGIS - User mailing list archive at Nabble.com.<br>
_______________________________________________<br>
postgis-users mailing list<br>
</div></div><a href="http://user/SendEmail.jtp?type=node&node=5002378&i=1" rel="nofollow" link="external" target="_blank">[hidden email]</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div>
<br>_______________________________________________
<br>postgis-users mailing list
<br><a href="http://user/SendEmail.jtp?type=node&node=5002378&i=2" rel="nofollow" link="external" target="_blank">[hidden email]</a>
<br><a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" rel="nofollow" link="external" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
<br>
<hr noshade size="1" color="#cccccc">
<div style="color:#444;font:12px tahoma,geneva,helvetica,arial,sans-serif">
<div style="font-weight:bold">If you reply to this email, your message will be added to the discussion below:</div>
<a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html" target="_blank" rel="nofollow" link="external">http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html</a>
</div>
<div style="color:#666;font:11px tahoma,geneva,helvetica,arial,sans-serif;margin-top:.4em;line-height:1.5em">
To unsubscribe from Why postgis doesn't use index during query?, <a href="" target="_blank" rel="nofollow" link="external">click here</a>.<br>
<a href="http://postgis.17.n6.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml" rel="nofollow" style="font:9px serif" target="_blank" link="external">NAML</a>
</div></blockquote></div><br></div>
<br/><hr align="left" width="300" />
View this message in context: <a href="http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html">Re: Why postgis doesn't use index during query?</a><br/>
Sent from the <a href="http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html">PostGIS - User mailing list archive</a> at Nabble.com.<br/>