[postgis-devel] reuse of values

[nicklas.aven at jordogskog.no]

Hallo
 
I have a question that I don't know if it is a pure postgresql-question.
Where in the process and how, does the planner descide what values to reuse in other parts of a query.
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?
 
in this example the querytime is almost the same for :
 
select sum(st_distance(linje1, linje2)+2) as theFirst,sum(st_distance(linje1, linje2)+2) as theSecond from 
 (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
 (select generate_series(1,200000,2) as a,
 generate_series(1,300000,3) as b,
 generate_series(1,400000,4) as c,
  generate_series(1,500000,5) as d) table1) table2

and
 

select sum(st_distance(linje1, linje2)+2) as theFirst from 
 (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
 (select generate_series(1,200000,2) as a,
 generate_series(1,300000,3) as b,
 generate_series(1,400000,4) as c,
  generate_series(1,500000,5) as d) table1) table2;

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:
 
select sum(st_distance(linje1, linje2)+1) as theFirst,sum(st_distance(linje1, linje2)+2) as theSecond from 
 (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
 (select generate_series(1,200000,2) as a,
 generate_series(1,300000,3) as b,
 generate_series(1,400000,4) as c,
  generate_series(1,500000,5) as d) table1) table2;

then it suddenly takes over 1000ms. 
 
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)
Am I right?
is there any trick how to handle it in more time-consuming examples.
 
 
/Nicklas
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20090418/1b1650b1/attachment.html>