[postgis-users] GIST index speed

[Peck, Brian]

Hi Paul,

Yeah, the 'SELECT count(*) FROM asdfs_track_point' was very quick. 

The asdfs_track_point table has 26 columns in it with the primary key,
the geometry field [which is currently filled with 2 dimensional POINT
geometry objects] and another field [track_index] being indexed to try
and speed up our queries. 

- Brian Peck
- 858-795-1398
- Software Engineer
- Lockheed Martin
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
Ramsey
Sent: Wednesday, June 04, 2008 4:21 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index speed

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
>
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users