<div dir="ltr">(sorry, a little precision: <span style="font-family:arial,sans-serif;font-size:13px">All st_* predicates internally use the && GIST operator: bbox intersects)</span></div><div class="gmail_extra"><br>
<br><div class="gmail_quote">On 22 January 2013 16:44, 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">In PSQL, \do will list you installed operators:<div><br></div><div><div>\do+</div><div> List of operators</div><div> Schema | Name | Left arg type | Right arg type | Result type | Description </div>
<div>--------+------+---------------+----------------+------------------+-------------</div><div> public | && | geography | geography | boolean | </div><div> public | && | geometry | geometry | boolean | </div>
<div> public | && | geometry | raster | boolean | </div><div> public | && | raster | geometry | boolean | </div><div> public | && | raster | raster | boolean | </div>
<div> public | &&& | geometry | geometry | boolean | </div><div> public | &< | geometry | geometry | boolean | </div><div> public | &< | raster | raster | boolean | </div>
<div> public | &<| | geometry | geometry | boolean | </div><div> public | &<| | raster | raster | boolean | </div><div> public | &> | geometry | geometry | boolean | </div>
<div> public | &> | raster | raster | boolean | </div><div> public | < | geography | geography | boolean | </div><div> public | < | geometry | geometry | boolean | </div>
<div> public | <#> | geometry | geometry | double precision | </div><div> public | <-> | geometry | geometry | double precision | </div><div> public | << | geometry | geometry | boolean | </div>
<div> public | << | raster | raster | boolean | </div><div> public | <<| | geometry | geometry | boolean | </div><div> public | <<| | raster | raster | boolean | </div>
</div><div><br></div><div>And this Pgis doc page describes them: <a href="http://postgis.net/docs/manual-2.0/reference.html#Operators" target="_blank">http://postgis.net/docs/manual-2.0/reference.html#Operators</a></div>
<div>
<br></div><div>All st_* predicates internally use GIST operator.</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>Nicolas</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra">
<br><br><div class="gmail_quote">On 22 January 2013 16:39, Pietro Rossin <span dir="ltr"><<a href="mailto:pierigis@gmail.com" target="_blank">pierigis@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">Is there any reference list for the GIST operators?<div>Thnx</div><div>Pietro</div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">2013/1/22 Pietro Rossin <span dir="ltr"><<a href="mailto:pierigis@gmail.com" target="_blank">pierigis@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><div><div dir="ltr">Superthank you!<div>It works and very fast!</div><div>Bie</div><div>Pietro</div>
</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="http://user/SendEmail.jtp?type=node&node=5002380&i=0" rel="nofollow" link="external" target="_blank">[hidden email]</a>></span><br>
<blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div>
<div dir="ltr"><div>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><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><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><div class="gmail_extra"><br><br><div class="gmail_quote"><div><div><div><div>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></div></div><blockquote style="border-left:2px solid #cccccc;padding:0 1em" class="gmail_quote"><div><div><div><div>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></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><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" rel="nofollow" link="external" target="_blank">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 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" link="external" target="_blank">NAML</a>
</div></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" target="_blank">Re: Why postgis doesn't use index during query?</a><div>
<div><br>
Sent from the <a href="http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html" target="_blank">PostGIS - User mailing list archive</a> at Nabble.com.<br></div></div><br></div></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>
<br></div></blockquote></div><br></div>
<br>_______________________________________________<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>
<br></blockquote></div><br></div>
</div></div></blockquote></div><br></div>