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

Erik Norvelle  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