[postgis-users] GIST index speed

[Paul Ramsey]

First, basic index concepts: when you are requesting the whole data
set, the index (any index) does *nothing* for your performance.  To
use an (obsolete) metaphor, if you are checking out *every* book in
the library, do you first go to the card catalog?

So your test case isn't testing anything index related.

Step one: is something seriously wrong with your database setup? Is
this query pretty fast: SELECT Count(*) FROM asdfs_track_point

It should be, counting to 42000 doesn't take long.

Step two: why *is* your SQL case slow? Doing anything 42000 times
shouldn't take long, particularly since one of your arguments is
simple and small.

Unless... your geometries are extremely big, or your tuples are
extremely wide.  Do you have a large large number of columns?  How
many vertices in your geometries?

P.

On Wed, Jun 4, 2008 at 4:05 PM, Peck, Brian <brian.peck at lmco.com> wrote:
> Hey all,
>
> I currently am working on a PostGIS database (Postgres 8.3) and the requests
> are going slower than expected.
>
> The table I'm querying off of has ~42000 entries in it, and the geometry
> field I'm using has a gist index on it. The query however is taking ~6
> seconds to return me the entries.
>
>
>
> I am making the query
>
>
>
> SELECT * FROM asdfs_track_point where
> within(the_geom,GeometryFromText('POLYGON((-180.0 -90.0,-180.0 90.0,180.0
> 90.0,180.0 -90.0,-180.0 -90.0))',4326));
>
>
>
> This is a test case and so I am doing the entire world to find out how long
> things should take, but I'm told that with only 42000 rows in the table it
> should be faster than 6 seconds.
>
>
>
> Anyone have any idea why it might be going slow, or a way to speed up the
> query?
>
>
>
> Thanks,
>
>
>
> - Brian Peck
>
> - 858-795-1398
>
> - Software Engineer
>
> - Lockheed Martin
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>