<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:m="http://schemas.microsoft.com/office/2004/12/omml" 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 12 (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</title>
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Thanks a lot Gregory<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>It did work as intended :)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>EXPLAIN SELECT * from streets_relation where this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Gives :<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>"Seq Scan on streets_relation (cost=0.00..4666.80
rows=45 width=758)"<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>" Filter: (this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'::geometry)"<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Whilst, as you explained well – the && worked with
the GiST<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>EXPLAIN SELECT * from streets_relation where this_geom
&&
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>"Index Scan using "This Geom" on
streets_relation (cost=0.00..6.01 rows=1 width=758)"<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>" Index Cond: (this_geom &&
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'::geometry)"<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>" Filter: (this_geom &&
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'::geometry)"<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Using the GiST I got a response in 30ms, whilst with the normal
‘=’ I got a response in 63ms, 100% difference :)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Thanks a lot for your help :)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Gregory
Williamson<br>
<b>Sent:</b> Wednesday, February 27, 2008 12:21 PM<br>
<b>To:</b> PostGIS Users Discussion; PostGIS Users Discussion<br>
<b>Subject:</b> RE: [postgis-users] GiST Index<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p style='margin-bottom:12.0pt'><span style='font-size:10.0pt'>Matthew --<br>
<br>
Try running<br>
ANALYZE streets_relation;<br>
<br>
This provides the statistics to the planner ...<br>
<br>
Use the ST_ functions (or && in older nomenclature) to invoke the
spatial index. Typically using an intersection or some such to get things
in an area. A GIST index is not a B-tree index and requires the &&
operator to be used (the ST_ functions in postGIS are wrappers that provide the
real spatial operator). "=" won't do that.<br>
<br>
Finally, if we have a very small table, or are asking for a *lot* of rows,
PostgreSQL will probably do a sequential scan anyway as it is faster.<br>
<br>
But my guess is you need to provide statistics for the planner and then use the
proper function call to invoke the GIST index.<br>
<br>
HTH,<br>
<br>
Greg Williamson<br>
Senior DBA<br>
DigitalGlobe Inc.<br>
<br>
-----Original Message-----<br>
From: postgis-users-bounces@postgis.refractions.net on behalf of Matthew Pulis<br>
Sent: Wed 2/27/2008 3:49 AM<br>
To: 'PostGIS Users Discussion'<br>
Subject: [postgis-users] GiST Index<br>
<br>
<br>
<br>
Hei all :)<br>
<br>
<br>
<br>
I have a table where it holds 2 Geometry type columns, this_geom, and<br>
last_geom.<br>
<br>
<br>
<br>
I will be using this table as a reference table where I will either pass<br>
thisgid or this_geom and would like all the other data extracted.<br>
<br>
<br>
<br>
However an EXPLAIN SELECT on a where this_geom =<br>
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094<br>
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724<br>
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000<br>
000000000'<br>
<br>
<br>
<br>
Gave me :<br>
<br>
<br>
<br>
"Seq Scan on streets_relation (cost=0.00..4666.80 rows=45
width=758)"<br>
<br>
" Filter: (this_geom =<br>
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D6094<br>
1B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724<br>
E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000<br>
000000000'::geometry)"<br>
<br>
<br>
<br>
Why rows does not equal to 1 but to 45?<br>
<br>
<br>
<br>
Does a GiST index in groups of 45? Can it be minimised to less groups<br>
please?<br>
<br>
<br>
<br>
This is the CREATE Table SQL text used :<br>
<br>
<br>
<br>
-- Table: streets_relation<br>
<br>
<br>
<br>
-- DROP TABLE streets_relation;<br>
<br>
<br>
<br>
CREATE TABLE streets_relation<br>
<br>
(<br>
<br>
last_geom geometry,<br>
<br>
x_last double precision,<br>
<br>
y_last double precision,<br>
<br>
z_last double precision,<br>
<br>
lastid integer NOT NULL,<br>
<br>
stseg bigint,<br>
<br>
lastname character varying(80),<br>
<br>
this_geom geometry,<br>
<br>
x_this double precision,<br>
<br>
y_this double precision,<br>
<br>
z_this double precision,<br>
<br>
x_new double precision,<br>
<br>
y_new double precision,<br>
<br>
z_new double precision,<br>
<br>
thisname character varying(80),<br>
<br>
thisgid integer NOT NULL,<br>
<br>
CONSTRAINT "Primarky Key GID" PRIMARY KEY (lastid, thisgid)<br>
<br>
)<br>
<br>
WITHOUT OIDS;<br>
<br>
ALTER TABLE streets_relation OWNER TO yancho;<br>
<br>
<br>
<br>
<br>
<br>
-- Index: "This Geom"<br>
<br>
<br>
<br>
-- DROP INDEX "This Geom";<br>
<br>
<br>
<br>
CREATE INDEX "This Geom"<br>
<br>
ON streets_relation<br>
<br>
USING gist<br>
<br>
(this_geom);<br>
<br>
<br>
<br>
<br>
<br>
When I did where thisgid = 10 this is the result I got :<br>
<br>
<br>
<br>
"Index Scan using "Primarky Key GID" on streets_relation
(cost=0.00..284.19<br>
rows=1 width=758)"<br>
<br>
" Index Cond: (thisgid = 10)"<br>
<br>
<br>
<br>
Any idea on what I can do to improve the performance please?<br>
<br>
<br>
<br>
Thanks and regards<br>
<br>
<br>
<br>
Matthew<br>
<br>
<br>
<br>
_____ <br>
<br>
I am using the free version of SPAMfighter for private users.<br>
It has removed 23646 spam emails to date.<br>
Paying users do not have this message in their emails.<br>
Try SPAMfighter <<a href="http://www.spamfighter.com/len">http://www.spamfighter.com/len</a>>
for free now!</span><o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<div class=MsoNormal align=center style='text-align:center'>
<hr size=2 width="100%" align=center>
</div>
<p class=MsoNormal>I am using the free version of SPAMfighter for private
users.<br>
It has removed 23855 spam emails to date.<br>
Paying users do not have this message in their emails.<br>
Try <a href="http://www.spamfighter.com/len">SPAMfighter</a> for free now!<o:p></o:p></p>
</div>
</body>
</html>