Re: update slow - Mailing list pgsql-general

Hi Richard,

Here are both ways:

dbmund=# EXPLAIN ANALYSE update estprod set
dbmund-# epr_costo=(select tcos from cost2est2003 where code=pro_code
and mes=epr_periodo)
dbmund-# where epr_periodo='200311';

  Seq Scan on estprod  (cost=0.00..9177.91 rows=8080 width=163) (actual
time=440.99..627139.23 rows=6867 loops=1)
    Filter: (epr_periodo = '200311'::bpchar)
    SubPlan
      ->  Index Scan using c2emes on cost2est2003  (cost=0.00..1532.53
rows=2 width=16) (actual time=66.60..91.19 rows=1 loops=6867)
            Index Cond: (mes = $1)
            Filter: (code = $0)
  Total runtime: 628557.56 msec
(7 rows)


dbmund=# explain analyze update estprod set
dbmund-# epr_costo= tcos
dbmund-# from cost2est2003
dbmund-# where code=pro_code and mes=epr_periodo
dbmund-# and epr_periodo='200311';

  Merge Join  (cost=10080.76..15930.98 rows=316 width=197) (actual
time=1191.89..4704.49 rows=6851 loops=1)
    Merge Cond: ("outer".code = "inner".pro_code)
    Join Filter: ("outer".mes = "inner".epr_periodo)
    ->  Index Scan using c2ecode on cost2est2003  (cost=0.00..4614.85
rows=99350 width=34) (actual time=0.16..2974.96 rows=99350 loops=1)
    ->  Sort  (cost=10080.76..10100.96 rows=8080 width=163) (actual
time=1191.62..1235.32 rows=55216 loops=1)
          Sort Key: estprod.pro_code
          ->  Seq Scan on estprod  (cost=0.00..9177.91 rows=8080
width=163) (actual time=396.88..1126.28 rows=6867 loops=1)
                Filter: (epr_periodo = '200311'::bpchar)
  Total runtime: 5990.34 msec
(9 rows)

Wow, update from is pretty faster than the first update, can't
understand why.

Thanks


Richard Huxton wrote:

> On Tuesday 11 November 2003 19:46, Josué Maldonado wrote:
>
>>This is the update command:
>>update estprod set epr_costo=(select tcos from cost2est2003 where
>>code=pro_code and mes=epr_periodo) where epr_periodo >='200301'
>>
>>The above filters the records to affect only 99157 rows, the update
>>takes hours and don't get done (I did cancel it), I changed the filter
>>to "epr_periodo ='200301'" to update only 9756 rows but still has more
>>than 10 minutes working.
>
>
> Can you post EXPLAIN ANALYSE <query> on this shorter one - that will show what
> PG is doing.
>
> One thing that might be worth looking at is using PG's non-standard
> UPDATE t1 SET c1=... FROM t2 ...
>


--
Josué Maldonado.