Re: Slow query

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
>