pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Erik Norvelle <erik@norvelle.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update performance ... is 200,000 updates per hour
Date: Tue, 2 Dec 2003 08:29:15 -0800 (PST)
Message-ID: <20031202082100.T87630@megazone.bigpanda.com> (raw)
In-Reply-To: <A461D6B8-24DF-11D8-BDFB-000A9583BF06@norvelle.net>
References: <A461D6B8-24DF-11D8-BDFB-000A9583BF06@norvelle.net>


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)



view thread (4+ messages)  latest in thread

Message-ID: <20031202082100.T87630@megazone.bigpanda.com>
Permalink:  ../20031202082100.T87630@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20031202082100.T87630@megazone.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone.bigpanda.com, erik@norvelle.net
  Subject: Re: Update performance ... is 200,000 updates per hour
  In-Reply-To: <20031202082100.T87630@megazone.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox