<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 14 (filtered medium)"><style><!--
/* Font Definitions */
@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:0in;
margin-bottom:.0001pt;
line-height:normal;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:windowtext;}
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:0in;
margin-bottom:5.75pt;
margin-left:0in;
line-height:115%;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:#00000A;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
line-height:normal;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
color:windowtext;}
p.western, li.western, div.western
{mso-style-name:western;
mso-margin-top-alt:auto;
margin-right:0in;
margin-bottom:5.75pt;
margin-left:0in;
line-height:115%;
font-size:10.0pt;
font-family:"Arial","sans-serif";
color:#00000A;}
p.cjk, li.cjk, div.cjk
{mso-style-name:cjk;
mso-margin-top-alt:auto;
margin-right:0in;
margin-bottom:5.75pt;
margin-left:0in;
line-height:115%;
font-size:10.0pt;
font-family:"Times New Roman","serif";
color:#00000A;}
p.ctl, li.ctl, div.ctl
{mso-style-name:ctl;
mso-margin-top-alt:auto;
margin-right:0in;
margin-bottom:5.75pt;
margin-left:0in;
line-height:115%;
font-size:12.0pt;
font-family:"Arial","sans-serif";
color:#00000A;
font-weight:bold;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></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=WordSection1><div><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I have a layer of grid cells and a layer of discreet points representing elevation samples. My grid layer has a total of about 430,000 cells, the elevation data contains about 320,000 points, and both datasets have spatial indexes on them. I need to join each grid cell with the nearest elevation point (within at most 30 feet of the cell’s center point). To accomplish this, I created the following query:<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'>SELECT DISTINCT ON (grid_rail_lines.pk_uid)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> grid_rail_lines.pk_uid<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>, elev_rail_combined.pk_uid AS el_id<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>, elev_rail_combined.elevation<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>FROM<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> grid_rail_lines JOIN elev_rail_combined<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> ON grid_rail_lines.the_geom<->elev_rail_combined.the_geom < 30<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>ORDER BY<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> grid_rail_lines.pk_uid ASC<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>, grid_rail_lines.the_geom<->elev_rail_combined.the_geom ASC<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'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>EXPLAIN provides the following information (also available at <a href="http://explain.depesz.com/s/Vsm">depesz</a>):<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'>Unique (cost=141973943356.82..142206589733.23 rows=434170 width=624)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> -> Sort (cost=141973943356.82..142090266545.03 rows=46529275283 width=624)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> Sort Key: grid_rail_lines.pk_uid, ((grid_rail_lines.the_geom <-> elev_rail_combined.the_geom))<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> -> Nested Loop (cost=0.00..4910712887.16 rows=46529275283 width=624)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'> Join Filter: ((grid_rail_lines.the_geom <-> elev_rail_combined.the_geom) < 30::double precision)<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 grid_rail_lines (cost=0.00..13202.70 rows=434170 width=484)<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 elev_rail_combined (cost=0.00..6220.05 rows=321505 width=140)<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'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I let this query run over the weekend and it took a total of 21 hours. These datasets are rather large so I expect it to take a long time, but I wonder if there is a more efficient way to conduct the join. As far as I can tell, I’ve structured the query as recommended in the documentation. Does anyone have ideas for how to improve performance? <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,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Spencer<o:p></o:p></span></p></div></div><HR>
<em>
<DIV><font style="FONT-FAMILY: ; FONT-SIZE: 11px"><span style="FONT-FAMILY: 'arial', 'sans-serif'; FONT-SIZE: 10pt">This e-mail and any files transmitted with it are confidential and are intended solely for the use of the individual or entity to whom they are addressed.<span> </span>If you are NOT the intended recipient and receive this communication, please delete this message and any attachments. Thank you.</span></font></em></DIV></body></html>