<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=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<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:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
p.yiv5167837039msoacetate, li.yiv5167837039msoacetate, div.yiv5167837039msoacetate
{mso-style-name:yiv5167837039msoacetate;
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;}
p.yiv5167837039msolistparagraph, li.yiv5167837039msolistparagraph, div.yiv5167837039msolistparagraph
{mso-style-name:yiv5167837039msolistparagraph;
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;}
p.yiv5167837039msonormal, li.yiv5167837039msonormal, div.yiv5167837039msonormal
{mso-style-name:yiv5167837039msonormal;
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;}
p.yiv5167837039msochpdefault, li.yiv5167837039msochpdefault, div.yiv5167837039msochpdefault
{mso-style-name:yiv5167837039msochpdefault;
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;}
p.yiv5167837039msonormal1, li.yiv5167837039msonormal1, div.yiv5167837039msonormal1
{mso-style-name:yiv5167837039msonormal1;
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;}
p.yiv5167837039msoacetate1, li.yiv5167837039msoacetate1, div.yiv5167837039msoacetate1
{mso-style-name:yiv5167837039msoacetate1;
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;}
p.yiv5167837039msolistparagraph1, li.yiv5167837039msolistparagraph1, div.yiv5167837039msolistparagraph1
{mso-style-name:yiv5167837039msolistparagraph1;
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;}
p.yiv5167837039msochpdefault1, li.yiv5167837039msochpdefault1, div.yiv5167837039msochpdefault1
{mso-style-name:yiv5167837039msochpdefault1;
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;}
p.yiv5167837039msonormal2, li.yiv5167837039msonormal2, div.yiv5167837039msonormal2
{mso-style-name:yiv5167837039msonormal2;
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.yiv5167837039msoacetate2, li.yiv5167837039msoacetate2, div.yiv5167837039msoacetate2
{mso-style-name:yiv5167837039msoacetate2;
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;}
p.yiv5167837039msolistparagraph2, li.yiv5167837039msolistparagraph2, div.yiv5167837039msolistparagraph2
{mso-style-name:yiv5167837039msolistparagraph2;
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;}
p.yiv5167837039msonormal3, li.yiv5167837039msonormal3, div.yiv5167837039msonormal3
{mso-style-name:yiv5167837039msonormal3;
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;}
p.yiv5167837039msochpdefault2, li.yiv5167837039msochpdefault2, div.yiv5167837039msochpdefault2
{mso-style-name:yiv5167837039msochpdefault2;
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;}
p.yiv5167837039msonormal11, li.yiv5167837039msonormal11, div.yiv5167837039msonormal11
{mso-style-name:yiv5167837039msonormal11;
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msoacetate11, li.yiv5167837039msoacetate11, div.yiv5167837039msoacetate11
{mso-style-name:yiv5167837039msoacetate11;
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msolistparagraph11, li.yiv5167837039msolistparagraph11, div.yiv5167837039msolistparagraph11
{mso-style-name:yiv5167837039msolistparagraph11;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Times New Roman",serif;
color:black;}
p.yiv5167837039msochpdefault11, li.yiv5167837039msochpdefault11, div.yiv5167837039msochpdefault11
{mso-style-name:yiv5167837039msochpdefault11;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:10.0pt;
font-family:"Times New Roman",serif;}
span.yiv5167837039msohyperlink
{mso-style-name:yiv5167837039msohyperlink;}
span.yiv5167837039msohyperlinkfollowed
{mso-style-name:yiv5167837039msohyperlinkfollowed;}
span.yiv5167837039htmlpreformattedchar
{mso-style-name:yiv5167837039htmlpreformattedchar;}
span.yiv5167837039msohyperlink1
{mso-style-name:yiv5167837039msohyperlink1;}
span.yiv5167837039msohyperlinkfollowed1
{mso-style-name:yiv5167837039msohyperlinkfollowed1;}
span.yiv5167837039htmlpreformattedchar1
{mso-style-name:yiv5167837039htmlpreformattedchar1;}
span.yiv5167837039emailstyle221
{mso-style-name:yiv5167837039emailstyle221;}
span.yiv5167837039emailstyle231
{mso-style-name:yiv5167837039emailstyle231;}
span.yiv5167837039emailstyle241
{mso-style-name:yiv5167837039emailstyle241;}
span.yiv5167837039emailstyle251
{mso-style-name:yiv5167837039emailstyle251;}
span.yiv5167837039emailstyle261
{mso-style-name:yiv5167837039emailstyle261;}
span.yiv5167837039emailstyle271
{mso-style-name:yiv5167837039emailstyle271;}
span.yiv5167837039emailstyle281
{mso-style-name:yiv5167837039emailstyle281;}
span.yiv5167837039emailstyle291
{mso-style-name:yiv5167837039emailstyle291;}
span.yiv5167837039emailstyle301
{mso-style-name:yiv5167837039emailstyle301;}
span.yiv5167837039emailstyle311
{mso-style-name:yiv5167837039emailstyle311;}
span.yiv5167837039emailstyle55
{mso-style-name:yiv5167837039emailstyle55;}
span.yiv5167837039msohyperlink2
{mso-style-name:yiv5167837039msohyperlink2;
color:blue;
text-decoration:underline;}
span.yiv5167837039msohyperlinkfollowed2
{mso-style-name:yiv5167837039msohyperlinkfollowed2;
color:purple;
text-decoration:underline;}
span.yiv5167837039htmlpreformattedchar2
{mso-style-name:yiv5167837039htmlpreformattedchar2;
font-family:Consolas;}
span.yiv5167837039msohyperlink11
{mso-style-name:yiv5167837039msohyperlink11;
color:#0563C1;
text-decoration:underline;}
span.yiv5167837039msohyperlinkfollowed11
{mso-style-name:yiv5167837039msohyperlinkfollowed11;
color:#954F72;
text-decoration:underline;}
span.yiv5167837039htmlpreformattedchar11
{mso-style-name:yiv5167837039htmlpreformattedchar11;
font-family:Consolas;
color:black;}
span.yiv5167837039emailstyle2211
{mso-style-name:yiv5167837039emailstyle2211;
color:windowtext;}
span.yiv5167837039emailstyle2311
{mso-style-name:yiv5167837039emailstyle2311;
color:#1F497D;}
span.yiv5167837039emailstyle2411
{mso-style-name:yiv5167837039emailstyle2411;
color:#1F497D;}
span.yiv5167837039emailstyle2511
{mso-style-name:yiv5167837039emailstyle2511;
color:windowtext;}
span.yiv5167837039emailstyle2611
{mso-style-name:yiv5167837039emailstyle2611;
color:#1F497D;}
span.yiv5167837039emailstyle2711
{mso-style-name:yiv5167837039emailstyle2711;
color:#1F497D;}
span.yiv5167837039emailstyle2811
{mso-style-name:yiv5167837039emailstyle2811;
color:#1F497D;}
span.yiv5167837039emailstyle2911
{mso-style-name:yiv5167837039emailstyle2911;
color:#1F497D;}
span.yiv5167837039emailstyle3011
{mso-style-name:yiv5167837039emailstyle3011;
color:#1F497D;}
span.yiv5167837039emailstyle3111
{mso-style-name:yiv5167837039emailstyle3111;
color:windowtext;}
span.yiv5167837039emailstyle551
{mso-style-name:yiv5167837039emailstyle551;
color:#1F497D;}
span.EmailStyle70
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle71
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle72
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle73
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle74
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
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-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Dear Regina,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Many thanks for this suggestion.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I ran the query in this form and for one raster it takes 3372s (~55 mins). I guess that is what I had anticipated from the single
tile exercise I ran with the alternate algorithm.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">This still seems a little too long however and so I have started to explore ways to improve upon this by pre-processing some of the
data. The cells of interest represent the ‘main channels’ in a drainage network. Although in future this network may change position from one output timestep to another (they are actually 1000l iterations of the model apart) in this particular variant the
position of these cells is static. With that in mind I decided to create a binary raster where the 1’s represent the channel cells (I had to do this in arcgis as QGIS does not appear to have a Con function in the raster calculator!) and then exported this
as a point layer. I then deleted the points coded zero and saved the shp file in QGIS. I imported the ntwork shp file into postgis (there are about 15500 15 points) and I am now running:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">CREATE TABLE mymodel.networkdep AS<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">SELECT filename, gid, ST_Value(rast, geom) val<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">FROM mymodel.deposition, mymodel.network<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">WHERE ST_Intersects(rast, geom)
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">ORDER BY gid, rid;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I will sum by raster (i.e. filename) using the new table but will settle for just having the relevant data for now. This took 2057s(~35
minutes!) to complete! <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">If this is the best way to do it I will explore the OGR library and try and hardcode the network point output directly into the model
or, more realistically, write a short routine to extract this automatically from the flow accumulation output rasters without recourse to a GIS.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I am learning a lot through this exercise, so thanks once again to all of you who have made suggestions, they are very much appreciated.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Best wishes<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Darrel
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Paragon Corporation<br>
<b>Sent:</b> 26 November 2015 05:08<br>
<b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">That timing seems much slower than I recall.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">FWIW expression based mapalgebra as I recall is slower than using the call back function approach. So you could try wrapping your CASE in a call
back function.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">However I think something else might be going on here and postgres might be repeating work. I forgot under what conditions it decides to reevaluate
a function call, I just remember being really surprised by it.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">To avoid that, you can try using a CTE, also you don't need that ST_Union call which for larger number of rasters is expensive, and you might even
generate a raster that is too big to compute.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I'm also guessing your rasts are all tiled the same, so you really don't need ST_Intersects, just use the same box operator<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">So try this:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">WITH foo AS (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">
</span><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">SELECT ST_SummaryStats( ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) ) As st<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> FROM mymodel.deposition INNER JOIN mymodel.concentrated ON ( deposition.rast ~= concentrated.rast )<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> WHERE deposition.rid=1</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">SELECT SUM( (st).sum )<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">FROM foo;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Hope that helps,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Regina<o:p></o:p></span></p>
<p class="MsoNormal"><a href="http://www.postgis.us"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">http://www.postgis.us</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><a href="http://postgis.net"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">http://postgis.net</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" 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 #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">]
<b>On Behalf Of </b>Darrel Maddy<br>
<b>Sent:</b> Wednesday, November 25, 2015 5:06 PM<br>
<b>To:</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">postgis-users@lists.osgeo.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">>;
Brent Wood <</span><a href="mailto:pcreso@yahoo.com"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">pcreso@yahoo.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">><br>
<b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Dear Brent,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I must confess that my attempts to do this are so far proving very unsuccessful<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">If I run the following query:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> WHERE ST_Intersects(deposition.rast, concentrated.rast) AND deposition.rid=1 ) foo ;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">It takes around 30 seconds to complete as I assume it is only looking at one tile(they are 256x256 pixels) i.e. rid 1. It is not easy
to check the sum – for that I need one complete raster.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">For the record this was marginally faster than<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> WHERE mymodel.deposition.filename='10_depo.tif' AND ST_UpperleftX(mymodel.deposition.rast) = ST_UpperleftX(mymodel.concentrated.rast)
AND <o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> ST_UpperleftY(mymodel.deposition.rast) = ST_UpperleftY(mymodel.deposition.rast) ) foo ;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Even after I built indexes for the clauses after the WHERE.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Now there are 144 tiles in each of the rasters I want to perform this operation on. Logic would therefore suggest this should take
~4500s<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">However when I perform the following query<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">SELECT (ST_SummaryStats(ST_Union(rast))).sum AS sum<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">FROM (SELECT ST_MapAlgebra(deposition.rast, concentrated.rast, 'CASE WHEN [rast2] > 0.0 THEN [rast1] ELSE NULL END' ) As rast<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> FROM mymodel.deposition, mymodel.concentrated<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> WHERE ST_Intersects(deposition.rast, concentrated.rast) AND deposition.filename='10_depo.tif' ) foo ;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">The query is still running after 18000s! I must therefore assume I have done something wrong but as you may have guessed the answer
eludes me.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Any further suggestions would be welcome but I will continue to try and find a solution as I have 135 rasters to perform this operations
on now and potentially many thousands more in the future.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Darrel<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><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 #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">]
<b>On Behalf Of </b>Darrel Maddy<br>
<b>Sent:</b> 24 November 2015 19:52<br>
<b>To:</b> Brent Wood <</span><a href="mailto:pcreso@yahoo.com"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">pcreso@yahoo.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">>;
</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">postgis-users@lists.osgeo.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><br>
<b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Dear Brent,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Many thanks. The data are tiled (256x256) hence the large number of rows from the original 135 tifs. I did not build any indexes however,
so I will do some reading and see how best to approach that (the threads you listed look useful so thanks for that).<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">I will run some additional mini queries limited to just one comparison and check using QGIS as you suggest – I probably should have
done that first!<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">My workstation has 64GB Ram and I would be surprised if it was significantly caching to disk. I also have a hexacore intel extreme
processor so I would not expect this to be hardware limited. I must confess I expected it to finish within a couple of hours.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Anyhow very many thanks. I will continue to explore and report back hopefully with positive news.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D">Darrel<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><a name="_MailEndCompose"></a><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 #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Brent Wood [</span><a href="mailto:pcreso@yahoo.com"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">mailto:pcreso@yahoo.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">]
<br>
<b>Sent:</b> 24 November, 2015 7:36 PM<br>
<b>To:</b> Darrel Maddy <</span><a href="mailto:darrel.maddy@newcastle.ac.uk"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">darrel.maddy@newcastle.ac.uk</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">>;
</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">postgis-users@lists.osgeo.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><br>
<b>Subject:</b> Re: [postgis-users] Help with SQL query?<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<div>
<div id="yui_3_16_0_1_1448337433794_89794">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">Indexing can improve performance by 100s of x, without them things can be slow. Also, did you tile the images when you imported them?
If not, then each iteration is working through all the pixels in the image, rather than a small subset. Essentially with tiles, you have a deep (long) table rather than a wide one. RDBMSs work better with lots of small records than a few wide ones, especially
when indexes are used.<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_93227">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_93228">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">This might help:<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_93229">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><a href="http://gis.stackexchange.com/questions/43053/how-to-speed-up-queries-for-raster-databases"><span style="font-family:"Verdana",sans-serif">http://gis.stackexchange.com/questions/43053/how-to-speed-up-queries-for-raster-databases</span></a><span style="font-family:"Verdana",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_94765">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_94766">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">and see the raster tutorial they mention for the SRTM data, as to how that is loaded into Postgis:<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_94767">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><a href="https://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01"><span style="font-family:"Verdana",sans-serif">https://trac.osgeo.org/postgis/wiki/WKTRasterTutorial01</span></a><span style="font-family:"Verdana",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_89859">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_90054">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">To test the logic (the syntax is correct or it wouldn't be working) you could add to the "where" clause an extra filter so that only
a small subset of the entire dataset is included (like just one QGIS operation) then compare this with the QGIS result.<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_91095">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_91168">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">That would be much faster that testing on the entire dataset. Once you know it is correct for the test case(s), then you can run it on
the complete set.<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92010">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92011">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">Note that some queries can build up large in-memory objects, so make sure your system is not swapping to disk, as that will also slow
things down (hugely).<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92207">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92208">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">Cheers<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92210">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_1_1448337433794_92211">
<p class="MsoNormal" style="margin-left:36.0pt;background:white"><span style="font-family:"Verdana",sans-serif;color:black">Brent<o:p></o:p></span></p>
</div>
</div>
</div>
</body>
</html>