DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=i6aqtIxT3PNAbXNa7FLBAniwsZmT0LyTT1SDgy7eZq1Jk5i4r9wD+fjXs502V4vjtEJPAUsi6yHyOUQnPIPQ504D+X/g+b51+tqQZ3xMiUqLMaaaram4VN/k7I+QZTJM9vJfkJNY8154u/an6/hlA3kGQ8WuZezf572O6EuEqvI=
Message-ID: <38242de9041023050832a57cbf@mail.gmail.com>
Date: Sat, 23 Oct 2004 06:08:04 -0600
From: Joshua Marsh <icub3d@gmail.com>
Reply-To: Joshua Marsh <icub3d@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query
In-Reply-To: <18707.1098477434@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <41795A35.4060802@paccomsys.com> <18707.1098477434@sss.pgh.pa.us>

Any time you run subqueries, it's going to slow down the update
process a lot.  Each record that is updated in source_song_title runs
two additional queries.  When I do large updates like this, I usualy
Run a transaction that will select all the new data into a new table
on a join.  For example

SELECT 
  a.*, 
  b.licensing_match_order, 
  b.affiliation_match_order, 
  d.title
INTO 
  updated_data 
FROM 
  source_song_title AS a
INNER JOIN
  source_system AS b
ON
  b.id = d.id
INNER JOIN
  source_song AS c
ON
  a.id = c.id
INNER JOIN
  source_title AS d
ON
  a.id = d.id

I'm not sure that query does what you want, but you get the idea. 
Then just drop the old table and rename the updated_data table.  This
way instead of doing a bunch of updates, you do one select and a
rename.

-Josh

On Fri, 22 Oct 2004 16:37:14 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Roger Ging <rging@paccomsys.com> writes:
> > update source_song_title set
> > source_song_title_id = nextval('source_song_title_seq')
> > ,licensing_match_order = (select licensing_match_order from
> > source_system where source_system_id = ss.source_system_id)
> > ,affiliation_match_order = (select affiliation_match_order from
> > source_system where source_system_id = ss.source_system_id)
> > ,title = st.title
> > from source_song_title sst
> > join source_song ss on ss.source_song_id = sst.source_song_id
> > join source_title st on st.title_id = sst.title_id
> > where source_song_title.source_song_id = sst.source_song_id;
> 
> Why is "source_song_title sst" in there?  To the extent that
> source_song_id is not unique, you are multiply updating rows
> because of the self-join.
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>