<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:x="urn:schemas-microsoft-com:office:excel" 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]-->
<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;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-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;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
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><o:p> </o:p></p>
<p class=MsoNormal><span lang=EN-GB>Hei all :)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>I have a table where it holds 2 Geometry
type columns, this_geom, and last_geom.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>I will be using this table as a reference
table where I will either pass thisgid or this_geom and would like all the
other data extracted.<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>However an EXPLAIN SELECT on a where
this_geom =
'01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Gave me :<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>"Seq Scan on streets_relation
(cost=0.00..4666.80 rows=45 width=758)"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>" Filter: (this_geom = '01050000E0777D00000100000001020000C00300000067DA64CD31AA224154740E5BC4D60941B3BA7124724E564000000000000000002152377FC8A92241D43FC27DA3D50941B3BA7124724E56400000000000000000D3C361E8A5A82241105C30638AD209410B44B0E305FB55400000000000000000'::geometry)"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Why rows does not equal to 1 but to 45?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Does a GiST index in groups of 45? Can it
be minimised to less groups please?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>This is the CREATE Table SQL text used :<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>-- Table: streets_relation<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>-- DROP TABLE streets_relation;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>CREATE TABLE streets_relation<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>(<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> last_geom geometry,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> x_last double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> y_last double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> z_last double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> lastid integer NOT NULL,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> stseg bigint,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> lastname character varying(80),<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> this_geom geometry,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> x_this double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> y_this double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> z_this double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> x_new double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> y_new double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> z_new double precision,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> thisname character varying(80),<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> thisgid integer NOT NULL,<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> CONSTRAINT "Primarky Key
GID" PRIMARY KEY (lastid, thisgid)<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>) <o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>WITHOUT OIDS;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>ALTER TABLE streets_relation OWNER TO
yancho;<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>-- Index: "This Geom"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>-- DROP INDEX "This Geom";<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>CREATE INDEX "This Geom"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> ON streets_relation<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> USING gist<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB> (this_geom);<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>When I did where thisgid = 10 this is the
result I got :<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>"Index Scan using "Primarky Key
GID" on streets_relation (cost=0.00..284.19 rows=1 width=758)"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>" Index Cond: (thisgid =
10)"<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Any idea on what I can do to improve the
performance please?<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Thanks and regards<o:p></o:p></span></p>
<p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p>
<p class=MsoNormal><span lang=EN-GB>Matthew<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p> </o:p></span></p>
<div class=MsoNormal align=center style='text-align:center'><span
style='font-size:12.0pt;font-family:"Times New Roman","serif"'>
<hr size=2 width="100%" align=center>
</span></div>
<p class=MsoNormal><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'>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 <a href="http://www.spamfighter.com/len">SPAMfighter</a> for free now!<o:p></o:p></span></p>
</div>
</body>
</html>