[postgis-users] GIST index speed
Peck, Brian
brian.peck at lmco.com
Wed Jun 4 17:22:10 PDT 2008
As far as I know we did not compile with debugging on, and swapping to
contains did not speed things up. However we are not using ST_Contains()
[just contains()]. These don't exist in the database (either the 8.2 I
am testing on - or an upgraded 8.3 I use locally)
Also when I rechecked the database that was supposed to be upgraded to
8.3 it was only on 8.2.
The log files seemed to only show anything if there was a problem (i.e.
bad password, non existent function, etc...) and didn't show anything
when I ran the successful but slow query.
Not sure if it will help but here is the schema for the table I am
querying against. The old lat/lon/alt fields are in there for the old
legacy database code that doesn't work with PostGIS.
Table "public.asdfs_track_point"
Column | Type |
Modifier
s
--------------------+------------------+--------------------------------
--------
---------------------------------
point_index | integer | not null default
nextval('asdfs_track_p
oint_point_index_seq'::regclass)
report_index | integer |
track_index | integer |
point_count | integer |
time | double precision |
latitude | double precision |
longitude | double precision |
altitude | double precision |
vel_east | real |
vel_north | real |
vel_up | real |
pos_sigma_east | real |
pos_sigma_north | real |
pos_sigma_up | real |
pos_cov_east_north | real |
pos_cov_east_up | real |
pos_cov_north_up | real |
vel_sigma_east | real |
vel_sigma_north | real |
vel_sigma_up | real |
vel_cov_east_north | real |
vel_cov_east_up | real |
vel_cov_north_up | real |
speed | double precision |
the_geom | geometry |
uturn | smallint |
Indexes:
"asdfs_track_point_pkey" PRIMARY KEY, btree (point_index)
"track_idx" btree (track_index)
"track_point_id" gist (the_geom)
Check constraints:
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'POINT'::text OR
the_geom IS NULL)
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)
- 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:53 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GIST index speed
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
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list