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

[Pietro Rossin]

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130122/dd1127e9/attachment.html>