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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Roger Ging <rging(at)paccomsys(dot)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
>