[postgis-users] Why postgis doesn't use index during query?

[Nicolas Ribot]

In PSQL, \do will list you installed operators:

\do+
                                List of operators
 Schema | Name | Left arg type | Right arg type |   Result type    |
Description
--------+------+---------------+----------------+------------------+-------------
 public | &&   | geography     | geography      | boolean          |
 public | &&   | geometry      | geometry       | boolean          |
 public | &&   | geometry      | raster         | boolean          |
 public | &&   | raster        | geometry       | boolean          |
 public | &&   | raster        | raster         | boolean          |
 public | &&&  | geometry      | geometry       | boolean          |
 public | &<   | geometry      | geometry       | boolean          |
 public | &<   | raster        | raster         | boolean          |
 public | &<|  | geometry      | geometry       | boolean          |
 public | &<|  | raster        | raster         | boolean          |
 public | &>   | geometry      | geometry       | boolean          |
 public | &>   | raster        | raster         | boolean          |
 public | <    | geography     | geography      | boolean          |
 public | <    | geometry      | geometry       | boolean          |
 public | <#>  | geometry      | geometry       | double precision |
 public | <->  | geometry      | geometry       | double precision |
 public | <<   | geometry      | geometry       | boolean          |
 public | <<   | raster        | raster         | boolean          |
 public | <<|  | geometry      | geometry       | boolean          |
 public | <<|  | raster        | raster         | boolean          |

And this Pgis doc page describes them:
http://postgis.net/docs/manual-2.0/reference.html#Operators

All st_* predicates internally use GIST operator.

Nicolas


On 22 January 2013 16:39, Pietro Rossin <pierigis at gmail.com> wrote:

> Is there any reference list for the GIST operators?
> Thnx
> Pietro
>
>
> 2013/1/22 Pietro Rossin <pierigis at gmail.com>
>
>> Superthank you!
>> It works and very fast!
>> Bie
>> Pietro
>>
>>
>> 2013/1/22 Nicolas Ribot-2 [via PostGIS] <[hidden email]<http://user/SendEmail.jtp?type=node&node=5002380&i=0>
>> >
>>
>>> Hi,
>>>
>>> GIST index is only used with GIST operators:
>>> &&, st_intersects, st_touches, etc.
>>>
>>> You should rewrite your query to define a geographic bounding box and
>>> test your data against it:
>>>
>>> SELECT the_geom, layer, id
>>>   FROM gauss_b.polilineegb
>>>   where the_geom && 'BOX(2365385 5075215, 2366019 5075752)'::box2d;
>>>
>>> Nicolas
>>>
>>>
>>>
>>> On 22 January 2013 15:38, Pietro Rossin <[hidden email]<http://user/SendEmail.jtp?type=node&node=5002378&i=0>
>>> > wrote:
>>>
>>>> Hello
>>>> I have a table with about 6.000.000 polilines records.
>>>> The geometry value is in the_geom column.
>>>> I made an index with this instruction
>>>>
>>>> *************
>>>> CREATE INDEX idx_polilineegb_geom
>>>>   ON gauss_b.polilineegb
>>>>   USING gist
>>>>   (the_geom );
>>>> *************
>>>>
>>>> If I try a query like this:
>>>> **********************
>>>> SELECT the_geom, layer, id
>>>>   FROM gauss_b.polilineegb
>>>>   where
>>>>   (st_xmin(the_geom)=2365385 and st_ymin(the_geom)=5075215) and
>>>> (st_xmax(the_geom)=2366019 and st_ymax(the_geom)=5075752);
>>>> **********************
>>>>
>>>> It takes ages to execute..
>>>> I take a look at the explain query it says:
>>>>
>>>> ************************
>>>> "Seq Scan on polilineegb  (cost=0.00..727911.08 rows=1 width=2924)"
>>>> "  Filter: ((st_xmin((the_geom)::box3d) = 2365385::double precision) AND
>>>> (st_ymin((the_geom)::box3d) = 5075215::double precision) AND
>>>> (st_xmax((the_geom)::box3d) = 2366019::double precision) AND
>>>> (st_ymax((the_geom)::box3d) = 5075752::double precision))"
>>>> ************************
>>>> This is a Seq Scan, so it doesn't use the index, right?
>>>> Why?
>>>> Is there some more performing index to be used??
>>>>
>>>> Thank you
>>>> Pietro
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> View this message in context:
>>>> http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377.html
>>>> Sent from the PostGIS - User mailing list archive at Nabble.com.
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5002378&i=1>
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> [hidden email] <http://user/SendEmail.jtp?type=node&node=5002378&i=2>
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>> ------------------------------
>>>  If you reply to this email, your message will be added to the
>>> discussion below:
>>>
>>> http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002378.html
>>>  To unsubscribe from Why postgis doesn't use index during query?, click
>>> here.
>>> NAML<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>
>>>
>>
>>
>> ------------------------------
>> View this message in context: Re: Why postgis doesn't use index during
>> query?<http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html>
>>
>> Sent from the PostGIS - User mailing list archive<http://postgis.17.n6.nabble.com/PostGIS-User-f3516033.html>at Nabble.com.
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130122/9927d0f4/attachment.html>