Re: Update performance ... is 200,000 updates per hour

On Tue, 2 Dec 2003, Erik Norvelle wrote:

> ** My question has to do with whether or not I am getting maximal speed
> out of PostgreSQL, or whether I need to perform further optimizations.
> I am currently getting about 200,000 updates per hour, and updating the
> entire 10 million rows thus requires 50 hours, which seems a bit much.

Well, it doesn't entirely surprise me much given the presumably 10 million
iterations of the index scan that it's doing. Explain analyze output (even
over a subset of the indethom table by adding a where clause) would
probably help to get better info.

I'd suggest seeing if something like:
update indethom set query_counter=...,sectref=s.clavis
FROM s2.sectiones s where
s2.sectiones.nomeoper = indethom.nomeoper and ...;
tries a join that might give a better plan.

> Here's the query I am running:
> update indethom
> set query_counter = nextval('s2.query_counter_seq'), -- Just
> for keeping track of how fast the query is running
> sectref = (select 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);
>
> Heres the query plan:
> QUERY PLAN
> ------------------------------------------------------------------------
> -------------
> Seq Scan on indethom (cost=0.00..1310352.72 rows=10631972 width=212)
> SubPlan
> -> Index Scan using sectiones_ndx on sectiones (cost=0.00..6.03
> rows=1 width=4)
> Index Cond: ((nomeoper = $0) AND (refere1a = $1) AND
> (refere1b = $2) AND (refere2a = $3) AND (refere2b = $4) AND (refere3a =
> $5) AND (refere3b = $6) AND (refere4a = $7) AND (refere4b = $8))
> (4 rows)