[postgis-users] GIST index speed

[Ries van Twisk]

> 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.

I would do a count on the complete set including the WHERE clause,  
much like this :
SELECT count(*) 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));

The reason is the eliminate the need for data transfer. I have seen  
occassions where
datatransfer (from DB to client) was the issue... I mention this  
because you didn't mention
your setup.

Second do an explain analyze..

Ries


>
>
> 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