Re: Update performance ... is 200,000 updates per hour what I should expect? - Mailing list pgsql-performance

Erik Norvelle <erik@norvelle.net> 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