<html>
<head>
<title></title>
<meta content="MSHTML 6.00.6000.16825" name="GENERATOR"></meta>
</head>
<body>
<div>Hallo</div>
<div> </div>
<div>I have a question that I don't know if it is a pure postgresql-question.</div>
<div>Where in the process and how, does the planner descide what values to reuse in other parts of a query.</div>
<div>I mean, if I do some calculation in the select-part and I want to use exectly the same calculation in the where-part. Will the value be recalculated or reused?</div>
<div> </div>
<div>in this example the querytime is almost the same for :</div>
<div> </div>
<div>select sum(st_distance(linje1, linje2)+2) as theFirst,sum(st_distance(linje1, linje2)+2) as theSecond from <br />
(select st_makeline(st_point(a,b),st_point(b,a)) as linje1,st_makeline(st_point(c,d),st_point(d,c)) as linje2 from<br />
(select generate_series(1,200000,2) as a,</div>
<div> generate_series(1,300000,3) as b,</div>
<div> generate_series(1,400000,4) as c,<br />
generate_series(1,500000,5) as d) table1) table2<br />
</div>
<div>and</div>
<div> </div>
<div>
<div>select sum(st_distance(linje1, linje2)+2) as theFirst from <br />
(select st_makeline(st_point(a,b),st_point(b,a)) as linje1,st_makeline(st_point(c,d),st_point(d,c)) as linje2 from<br />
(select generate_series(1,200000,2) as a,</div>
<div> generate_series(1,300000,3) as b,</div>
<div> generate_series(1,400000,4) as c,<br />
generate_series(1,500000,5) as d) table1) table2;<br />
</div></div>
<div>it takes in both cases about 600ms at my computer. this I interpret as the planer reuses the value of st_distance from theFirst to theSecond, but if I change the +2 in one of them to +1 like this:</div>
<div> </div>
<div>select sum(st_distance(linje1, linje2)+1) as theFirst,sum(st_distance(linje1, linje2)+2) as theSecond from <br />
(select st_makeline(st_point(a,b),st_point(b,a)) as linje1,st_makeline(st_point(c,d),st_point(d,c)) as linje2 from<br />
(select generate_series(1,200000,2) as a,
<div> generate_series(1,300000,3) as b,</div>
<div> generate_series(1,400000,4) as c,<br />
generate_series(1,500000,5) as d) table1) table2;<br />
</div></div>
<div>then it suddenly takes over 1000ms. </div>
<div> </div>
<div>My conclusion is that the planer knows how to reuse the whole value but not just the st_distance-calculation. So if I change some part it has to recalculate st_distance all over again. (The difference of 400ms is not from the calculation of +1 because the same query with just the st_distance-calculation without the addition-exercise also uses about 600ms, so the addition seems to be done in no time. but it distracts the planner)</div>
<div>Am I right?</div>
<div>is there any trick how to handle it in more time-consuming examples.</div>
<div> </div>
<div> </div>
<div>/Nicklas</div>
<div> </div>
</body>
</html>