<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns1="urn:schemas-microsoft-com:office:smarttags">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Comic Sans MS";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:595.3pt 841.9pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-AU link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hi all<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I am fairly new to postGIS and postgreSQL, so I am still
bumbling through a few performance issues at the moment.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I am having some trouble with doing an inner join on a
spatial table back with a normal table, and from what I can see in the query analyser,
it seems doesn’t seem to be using my indexes as efficiently as it should
be.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>The query is as follows:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>SELECT "PARCEL_VIEW"."UFI",
"PARCEL_VIEW"."UFI_OLD", "PARCEL_VIEW".version,
"PARCEL_VIEW"."PFI",
"PARCEL_VIEW"."CENTROID_PFI",
"PARCEL_VIEW"."Z_LEVEL",
"PARCEL_VIEW"."FEATURE_TYPE",
"PARCEL_VIEW"."FEATURE_QUALITY_ID", "PARCEL_VIEW"."PFI_CREATED",
"PARCEL_VIEW"."UFI_CREATED","PARCEL".spi,
"PARCEL_VIEW".geometry<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> FROM "PARCEL_VIEW",
"Coord","PARCEL"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> WHERE "PARCEL_VIEW".geometry &&
expand(geomfromtext(((('POINT('::text ||
"Coord"."Long"::text) || ' '::text) ||
"Coord"."Lat"::text) || ')'::text, 4019), 0.1::double
precision) <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND distance(geomfromtext(((('POINT('::text ||
"Coord"."Long"::text) || ' '::text) ||
"Coord"."Lat"::text) || ')'::text, 4019),
"PARCEL_VIEW".geometry) < 0.1::double precision <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>AND "PARCEL".view_pfi = "PARCEL_VIEW"."PFI";<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I am using a table with one row with a lat and a long value
to select all the shapes in the parcel_view table within 0.1 degrees.
Parcel_view does not hold the details I want, which are held in the PARCEL
table, which seems to be where the problem comes in. Running the query
without the join to the PARCEL table is very fast, but it slows down dramatically
when adding it.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>PARCEL_VIEW has two indexes on it, a GIST one on the
geometry column, and a btree one on the PFI column. PARCEL has a btree
index on the VIEW_PFI column. I have reduced the RANDOM_PAGE_COST down to
1.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Here is the output from the explain<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"Hash Join (cost=32.56..240113.97 rows=5
width=321)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>" Hash Cond: (("PARCEL".view_pfi)::text
= "PARCEL_VIEW"."PFI")"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>" -> Seq Scan on
"PARCEL" (cost=0.00..224592.23 rows=3097823 width=36)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>" -> Hash (cost=32.50..32.50
rows=5 width=299)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>" ->
Nested <st1:place w:st="on">Loop</st1:place> (cost=2.74..32.50 rows=5
width=299)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
Join Filter: (distance(geometryfromtext((((('POINT('::text ||
("Coord"."Long")::text) || ' '::text) ||
("Coord"."Lat")::text) || ')'::text), 4019),
"PARCEL_VIEW".geometry) < 0.1::double precision)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
-> Seq Scan on "Coord" (cost=0.00..1.01 rows=1
width=16)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
-> Bitmap Heap Scan on "PARCEL_VIEW" (cost=2.74..30.72
rows=14 width=299)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
Filter: ("PARCEL_VIEW".geometry &&
expand(geometryfromtext((((('POINT('::text || ("Coord"."Long")::text)
|| ' '::text) || ("Coord"."Lat")::text) || ')'::text),
4019), 0.1::double precision))"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
-> Bitmap Index Scan on "PARCEL_VIEW_Idx"
(cost=0.00..2.73 rows=14 width=0)"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>"
Index Cond: ("PARCEL_VIEW".geometry &&
expand(geometryfromtext((((('POINT('::text ||
("Coord"."Long")::text) || ' '::text) ||
("Coord"."Lat")::text) || ')'::text), 4019), 0.1::double
precision))"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I assume there is something fairly simple that I am missing,
so any help would be most appreciated.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Cheers<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'>James Kelly<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=3 face=Arial><span lang=EN-US style='font-size:
12.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face="Comic Sans MS"><span style='font-size:
10.0pt'><o:p> </o:p></span></font></p>
</div>
</body>
</html>
<br><hr size=1>Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.<br><a href=http://www.mailguard.com.au/tt>http://www.mailguard.com.au/tt</a><br>
<!-- MailGuard Message ID: 478d40c62c54a6 - use this number for reporting -->
<br> <br>