[postgis-users] Indexes on table with 200,000 rows

[Paul Ramsey]

You either have a small table (which 200k rows is not necessarily) or
your bounding box is a large % of your data (which it might be, that
looks like a big range).

P.

On Fri, Jul 17, 2009 at 9:58 AM, Paul Austin<mail-lists at revolsys.com> wrote:
> I have a table with a geometry column in BC Albers (3005) for which I have
> created a GIST index. I'm using postgresql 8.3.7 with Postgis 1.3.5-1.
>
> CREATE INDEX my_table_geom_gist
>   ON my_table
>   USING gist
>   (geom);
>
> I ran the VACUUM ANALYZE on the table.
>
> When I run the following it shows a sequential scan rather than an index
> scan.
> explain SELECT  geom FROM my_table WHERE geom && 'BOX3D(250982 1754957,
> 1369941 482671)'::box3d
>
> Ultimately I want to be able to query using the ST_Intersects function but
> if the index is not being used with the && operator then it's unlikely to
> using the index with ST_Intersects.
>
> In some forum posts there was a gist_geometry_ops option when creating
> indexes but that doesn't seem to work with the version I have.
>
> What are the real secrets to get postgis to use spatial indexes? Or do I
> just not have enough data in my tables?
>
> Paul
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>