CODEWITHSUNDEEP
sqlpostgresql
osh
osh

Reputation: 1261

Is the this function executed twice?

In the code below:

select t.id, ST_AsText(t.geom) AS tgeom, s.name, ST_Length(ST_ShortestLine(t.geom,s.geom)) AS short,t.par
from teta t, str_lines s
Where ST_Length(ST_ShortestLine(t.geom,s.geom))<200

Is ST_Length(ST_ShortestLine(t.geom,s.geom)) executed twice ?

Upvotes: 0

Views: 108

Answers (3)

wildplasser
wildplasser

Reputation: 44240

You could try packing it into a subquery, but I fear the optimiser will first create the cartesian product {teta * str_lines} before applying the join condition.

SELECT id, tgeom, name, short, par
FROM( select t.id
    , ST_AsText(t.geom) AS tgeom
    , s.name
    , ST_Length(ST_ShortestLine(t.geom,s.geom)) AS short
    , t.par
    from teta t, str_lines s
    ) pair
Where pair.short < 200

BTW: what does the JOIN syntax version do ?:

select t.id
  , ST_AsText(t.geom) AS tgeom
  , s.name
  , ST_Length(ST_ShortestLine(t.geom,s.geom)) AS short
  ,t.par
from teta t
JOIN str_lines s ON ST_Length(ST_ShortestLine(t.geom,s.geom))<200
   ;

Upvotes: 0

Woot4Moo
Woot4Moo

Reputation: 24316

Of course it is executed twice.

Once in your where clause and once in your column aliasing code.

That sucks..so i guess it would be better performance-wise to make my own function and cache ST_Length(ST_ShortestLine(t.geom,s.geom)), huh?

Consider the following:

create table st_length_temp as select ST_Length(ST_ShortestLine(t.geom,s.geom)) as length
from dual; --or whatever your equivalent of Oracle's dual is.

Now your query becomes: 

select t.id, ST_AsText(t.geom) AS tgeom, s.name,l.length AS short,t.par
from teta t, str_lines s, st_length_temp l
Where l.length < 200

Upvotes: 1

xeo
xeo

Reputation: 832

good question. but if instead you use 'WHERE short < 200' then it won't need to calculate it twice.

Upvotes: 1

Related Questions