pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Erik Norvelle <erik@norvelle.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Update performance ... is 200, 000 updates per hour what I should expect?
Date: 02 Dec 2003 11:40:51 -0500
Message-ID: <87u14j9nqk.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <A461D6B8-24DF-11D8-BDFB-000A9583BF06@norvelle.net>
References: <A461D6B8-24DF-11D8-BDFB-000A9583BF06@norvelle.net>

Erik Norvelle <erik@norvelle.net> writes:

> 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)

Firstly, you might try running "vacuum full" on both tables. If there are tons
of extra dead records that are left-over they could be slowing down the
update.

This isn't the fastest possible plan but it's pretty good.

You might be able to get it somewhat faster using the non-standard "from"
clause on the update statement.

update indethom
   set sectref = clavis
  from sectiones
 where sectiones.nomeoper = indethom.nomeoper
   and sectiones.refere1a = indethom.refere1a
   and sectiones.refere1b = indethom.refere1b
   and sectiones.refere2a = indethom.refere2a
   and sectiones.refere2b = indethom.refere2b
   and sectiones.refere3a = indethom.refere3a
   and sectiones.refere3b = indethom.refere3b
   and sectiones.refere4a = indethom.refere4a
   and sectiones.refere4b = indethom.refere4b

This might be able to use a merge join which will take longer to get started
because it has to sort both tables, but might finish faster.

You might also try just paring the index down to just the two or three most
useful columns. Is it common that something matches refere1a and refere1b but
doesn't match the remaining? A 8-column index is a lot of overhead. I'm not
sure how much that effects lookup times but it might be substantial.


-- 
greg




view thread (4+ messages)

Message-ID: <87u14j9nqk.fsf@stark.dyndns.tv>
Permalink:  ../87u14j9nqk.fsf@stark.dyndns.tv/
Also on:    postgresql.org/message-id/87u14j9nqk.fsf@stark.dyndns.tv

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, erik@norvelle.net
  Subject: Re: Update performance ... is 200, 000 updates per hour what I should expect?
  In-Reply-To: <87u14j9nqk.fsf@stark.dyndns.tv>
  References: <A461D6B8-24DF-11D8-BDFB-000A9583BF06@norvelle.net> <87u14j9nqk.fsf@stark.dyndns.tv>

* 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