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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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 >> [email protected] >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
