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