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

[Pietro Rossin]

Superthank you!
It works and very fast!
Bie
Pietro


2013/1/22 Nicolas Ribot-2 [via PostGIS] <
ml-node+s17n5002378h42 at n6.nabble.com>

> 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<http://postgis.17.n6.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5002377&code=cGllcmlnaXNAZ21haWwuY29tfDUwMDIzNzd8MjMwODY1MTQ5>
> .
> 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: http://postgis.17.n6.nabble.com/Why-postgis-doesn-t-use-index-during-query-tp5002377p5002380.html
Sent from the PostGIS - User mailing list archive at Nabble.com.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130122/e4341036/attachment.html>