Re: [PERFORM] Update performance ... is 200,000 updates per hour what I should expect?

[Tom Lane]

Erik Norvelle <[EMAIL PROTECTED]> writes:
> update indethom
>       set query_counter =3D nextval('s2.query_counter_seq'),           -- Just=
> =20=20
> for keeping track of how fast the query is running
>       sectref =3D (select clavis from s2.sectiones where
>               s2.sectiones.nomeoper =3D indethom.nomeoper
>               and s2.sectiones.refere1a =3D indethom.refere1a and=20=20
> s2.sectiones.refere1b =3D indethom.refere1b
>               and s2.sectiones.refere2a =3D indethom.refere2a  and=20=20
> s2.sectiones.refere2b =3D indethom.refere2b
>               and s2.sectiones.refere3a =3D indethom.refere3a  and=20=20
> s2.sectiones.refere3b =3D indethom.refere3b
>               and s2.sectiones.refere4a =3D indethom.refere4a and=20=20
> s2.sectiones.refere4b =3D indethom.refere4b);

This is effectively forcing a nestloop-with-inner-indexscan join.  You
might be better off with

update indethom
        set query_counter = nextval('s2.query_counter_seq'),
        sectref = sectiones.clavis
from s2.sectiones
where
                s2.sectiones.nomeoper = indethom.nomeoper
                and s2.sectiones.refere1a = indethom.refere1a and  
s2.sectiones.refere1b = indethom.refere1b
                and s2.sectiones.refere2a = indethom.refere2a  and  
s2.sectiones.refere2b = indethom.refere2b
                and s2.sectiones.refere3a = indethom.refere3a  and  
s2.sectiones.refere3b = indethom.refere3b
                and s2.sectiones.refere4a = indethom.refere4a and  
s2.sectiones.refere4b = indethom.refere4b;

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])