[postgis-users] GIST index speed

[Paul Ramsey]

Did you compile with debugging on? Doing a ST_Within test on 46000
things against a bbox should not take long at all.  Something else is
slowing things down. What shows up in your postgresql log files?  What
happens if you invert it and run ST_Contains instead?

P

On Wed, Jun 4, 2008 at 4:43 PM, Peck, Brian <brian.peck at lmco.com> wrote:
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>