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);
>
> Here�s 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)