[postgis-users] Why postgis doesn't use index during query?
Nicolas Ribot
nicolas.ribot at gmail.com
Tue Jan 22 07:44:47 PST 2013
(sorry, a little precision: All st_* predicates internally use the && GIST
operator: bbox intersects)
On 22 January 2013 16:44, Nicolas Ribot <nicolas.ribot at gmail.com> wrote:
> 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/8b8913b6/attachment.html>
More information about the postgis-users
mailing list