[postgis-users] GIST index speed
Peck, Brian
brian.peck at lmco.com
Wed Jun 4 17:51:19 PDT 2008
I'm gonna upgrade the Database tomorrow morning and I'll let you all
know if the ST functions speed up the query - I'll so narrow down the
test case to be a section where there is data as apposed to the entire
world.
Thanks for the help today, talk to you all tomorrow.
- Brian Peck
- 858-795-1398
- Software Engineer
- Lockheed Martin
________________________________
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Gregory Williamson
Sent: Wednesday, June 04, 2008 5:36 PM
To: PostGIS Users Discussion; PostGIS Users Discussion
Subject: RE: [postgis-users] GIST index speed
ST_ functions are modern variants of older functions -- they include the
bounding box check that eliminates most unwanted candidates from a
search.
So try something like:
SELECT * FROM asdfs_track_point where the_geom && 'BOX3D(? ?, ?
?)'::box3d AND
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));
where the question marks are the limits of your area of interest (I
can't find the definition of what BOX3D wants for the life of me, sorry)
HTH,
Greg Williamson
Senior DBA
DigitalGlobe
Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information and must be protected in
accordance with those provisions. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.
(My corporate masters made me say this.)
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Peck,
Brian
Sent: Wed 6/4/2008 5:22 PM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] GIST index speed
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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080604/7c3cc0f2/attachment.html>
More information about the postgis-users
mailing list