Re: update statement with unacceptable performance

From: Kay Kanekowski <kay.kanekowski_at_web.de>
Date: Wed, 12 Jun 2013 01:18:53 -0700 (PDT)
Message-ID: <a68c4f88-d862-4681-8976-cc627b532077_at_googlegroups.com>

Hallo Juerg,
do you know that you produce a cartesian product of 218.037 x 30.664 = 668.5886.568 rows ? And you do it 3 times.

As Charles said, you don't need sqrt(power(..))>0.

So i create a temp table like this:

create temp_x1_y1
as

select g.x1, 
       g.y1, 
       p.x1 as new_x1, 
       p.y1 as new_y1
  from point p,  
       geometry g

 where sqrt(power(g.x1-p.x1,2) + power(g.y1-p.y1,2)) < 0.0007 ;

Ok, it is the cartesian product too, but only 1 time.

And with this i would try something like this update geometry g
set

   (x1, y1) = (select new_x1, new_y1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1)  where exists (select 1 from temp_x1_y1 t where t.x1 = g.x1 and t.y1 = g.y1);

hth
bis denn
Kay Received on Wed Jun 12 2013 - 10:18:53 CEST