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

[Nicolas Ribot]

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 <pierigis at gmail.com> 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
> 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/8cc9e5b1/attachment.html>