<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] GIST index speed</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>ST_ functions are modern variants of older functions -- they include the bounding box check that eliminates most unwanted candidates from a search.<BR>
<BR>
So try something like:<BR>
<BR>
SELECT * FROM asdfs_track_point where the_geom && 'BOX3D(? ?, ? ?)'::box3d AND<BR>
within(the_geom,GeometryFromText('POLYGON((-180.0 -90.0,-180.0<BR>
90.0,180.0 90.0,180.0 -90.0,-180.0 -90.0))',4326));<BR>
<BR>
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)<BR>
<BR>
HTH,<BR>
<BR>
Greg Williamson<BR>
Senior DBA<BR>
DigitalGlobe<BR>
<BR>
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.<BR>
<BR>
(My corporate masters made me say this.)<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Peck, Brian<BR>
Sent: Wed 6/4/2008 5:22 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: RE: [postgis-users] GIST index speed<BR>
<BR>
As far as I know we did not compile with debugging on, and swapping to<BR>
contains did not speed things up. However we are not using ST_Contains()<BR>
[just contains()]. These don't exist in the database (either the 8.2 I<BR>
am testing on - or an upgraded 8.3 I use locally)<BR>
<BR>
Also when I rechecked the database that was supposed to be upgraded to<BR>
8.3 it was only on 8.2.<BR>
<BR>
The log files seemed to only show anything if there was a problem (i.e.<BR>
bad password, non existent function, etc...) and didn't show anything<BR>
when I ran the successful but slow query.<BR>
<BR>
Not sure if it will help but here is the schema for the table I am<BR>
querying against. The old lat/lon/alt fields are in there for the old<BR>
legacy database code that doesn't work with PostGIS.<BR>
<BR>
Table "public.asdfs_track_point"<BR>
Column | Type |<BR>
Modifier<BR>
s<BR>
--------------------+------------------+--------------------------------<BR>
--------<BR>
---------------------------------<BR>
point_index | integer | not null default<BR>
nextval('asdfs_track_p<BR>
oint_point_index_seq'::regclass)<BR>
report_index | integer |<BR>
track_index | integer |<BR>
point_count | integer |<BR>
time | double precision |<BR>
latitude | double precision |<BR>
longitude | double precision |<BR>
altitude | double precision |<BR>
vel_east | real |<BR>
vel_north | real |<BR>
vel_up | real |<BR>
pos_sigma_east | real |<BR>
pos_sigma_north | real |<BR>
pos_sigma_up | real |<BR>
pos_cov_east_north | real |<BR>
pos_cov_east_up | real |<BR>
pos_cov_north_up | real |<BR>
vel_sigma_east | real |<BR>
vel_sigma_north | real |<BR>
vel_sigma_up | real |<BR>
vel_cov_east_north | real |<BR>
vel_cov_east_up | real |<BR>
vel_cov_north_up | real |<BR>
speed | double precision |<BR>
the_geom | geometry |<BR>
uturn | smallint |<BR>
Indexes:<BR>
"asdfs_track_point_pkey" PRIMARY KEY, btree (point_index)<BR>
"track_idx" btree (track_index)<BR>
"track_point_id" gist (the_geom)<BR>
Check constraints:<BR>
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)<BR>
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =<BR>
'POINT'::text OR<BR>
the_geom IS NULL)<BR>
"enforce_srid_the_geom" CHECK (srid(the_geom) = 4326)<BR>
<BR>
- Brian Peck<BR>
- 858-795-1398<BR>
- Software Engineer<BR>
- Lockheed Martin<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net<BR>
[<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of Paul<BR>
Ramsey<BR>
Sent: Wednesday, June 04, 2008 4:53 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] GIST index speed<BR>
<BR>
Did you compile with debugging on? Doing a ST_Within test on 46000<BR>
things against a bbox should not take long at all. Something else is<BR>
slowing things down. What shows up in your postgresql log files? What<BR>
happens if you invert it and run ST_Contains instead?<BR>
<BR>
P<BR>
<BR>
On Wed, Jun 4, 2008 at 4:43 PM, Peck, Brian <brian.peck@lmco.com> wrote:<BR>
> Hi Paul,<BR>
><BR>
> Yeah, the 'SELECT count(*) FROM asdfs_track_point' was very quick.<BR>
><BR>
> The asdfs_track_point table has 26 columns in it with the primary key,<BR>
> the geometry field [which is currently filled with 2 dimensional POINT<BR>
> geometry objects] and another field [track_index] being indexed to try<BR>
> and speed up our queries.<BR>
><BR>
> - Brian Peck<BR>
> - 858-795-1398<BR>
> - Software Engineer<BR>
> - Lockheed Martin<BR>
> -----Original Message-----<BR>
> From: postgis-users-bounces@postgis.refractions.net<BR>
> [<A HREF="mailto:postgis-users-bounces@postgis.refractions.net">mailto:postgis-users-bounces@postgis.refractions.net</A>] On Behalf Of<BR>
Paul<BR>
> Ramsey<BR>
> Sent: Wednesday, June 04, 2008 4:21 PM<BR>
> To: PostGIS Users Discussion<BR>
> Subject: Re: [postgis-users] GIST index speed<BR>
><BR>
> First, basic index concepts: when you are requesting the whole data<BR>
> set, the index (any index) does *nothing* for your performance. To<BR>
> use an (obsolete) metaphor, if you are checking out *every* book in<BR>
> the library, do you first go to the card catalog?<BR>
><BR>
> So your test case isn't testing anything index related.<BR>
><BR>
> Step one: is something seriously wrong with your database setup? Is<BR>
> this query pretty fast: SELECT Count(*) FROM asdfs_track_point<BR>
><BR>
> It should be, counting to 42000 doesn't take long.<BR>
><BR>
> Step two: why *is* your SQL case slow? Doing anything 42000 times<BR>
> shouldn't take long, particularly since one of your arguments is<BR>
> simple and small.<BR>
><BR>
> Unless... your geometries are extremely big, or your tuples are<BR>
> extremely wide. Do you have a large large number of columns? How<BR>
> many vertices in your geometries?<BR>
><BR>
> P.<BR>
><BR>
> On Wed, Jun 4, 2008 at 4:05 PM, Peck, Brian <brian.peck@lmco.com><BR>
wrote:<BR>
>> Hey all,<BR>
>><BR>
>> I currently am working on a PostGIS database (Postgres 8.3) and the<BR>
> requests<BR>
>> are going slower than expected.<BR>
>><BR>
>> The table I'm querying off of has ~42000 entries in it, and the<BR>
> geometry<BR>
>> field I'm using has a gist index on it. The query however is taking<BR>
~6<BR>
>> seconds to return me the entries.<BR>
>><BR>
>><BR>
>><BR>
>> I am making the query<BR>
>><BR>
>><BR>
>><BR>
>> SELECT * FROM asdfs_track_point where<BR>
>> within(the_geom,GeometryFromText('POLYGON((-180.0 -90.0,-180.0<BR>
> 90.0,180.0<BR>
>> 90.0,180.0 -90.0,-180.0 -90.0))',4326));<BR>
>><BR>
>><BR>
>><BR>
>> This is a test case and so I am doing the entire world to find out<BR>
how<BR>
> long<BR>
>> things should take, but I'm told that with only 42000 rows in the<BR>
> table it<BR>
>> should be faster than 6 seconds.<BR>
>><BR>
>><BR>
>><BR>
>> Anyone have any idea why it might be going slow, or a way to speed up<BR>
> the<BR>
>> query?<BR>
>><BR>
>><BR>
>><BR>
>> Thanks,<BR>
>><BR>
>><BR>
>><BR>
>> - Brian Peck<BR>
>><BR>
>> - 858-795-1398<BR>
>><BR>
>> - Software Engineer<BR>
>><BR>
>> - Lockheed Martin<BR>
>><BR>
>><BR>
>><BR>
>> _______________________________________________<BR>
>> postgis-users mailing list<BR>
>> postgis-users@postgis.refractions.net<BR>
>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
>><BR>
>><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>