<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<title>RE: [postgis-users] GIST index speed</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Andale Mono";
panose-1:2 11 5 9 0 0 0 0 0 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Andale Mono";
color:blue;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>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.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>Thanks for the
help today, talk to you all tomorrow.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'><o:p> </o:p></span></font></p>
<div>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>- Brian Peck</span></font><font
color=blue><span style='color:blue'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>- 858-795-1398</span></font><font
color=blue><span style='color:blue'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>- Software
Engineer</span></font><font color=blue><span style='color:blue'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=blue face="Andale Mono"><span
style='font-size:10.0pt;font-family:"Andale Mono";color:blue'>- Lockheed Martin</span></font><o:p></o:p></p>
</div>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Gregory Williamson<br>
<b><span style='font-weight:bold'>Sent:</span></b> Wednesday, June 04, 2008
5:36 PM<br>
<b><span style='font-weight:bold'>To:</span></b> PostGIS Users Discussion;
PostGIS Users Discussion<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: [postgis-users] GIST
index speed</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p style='margin-bottom:12.0pt'><font size=2 face="Times New Roman"><span
style='font-size:10.0pt'>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></span></font><o:p></o:p></p>
</div>
</body>
</html>