From: Joshua Marsh <icub3d@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query
Date: Sat, 23 Oct 2004 06:08:04 -0600
Message-ID: <38242de9041023050832a57cbf@mail.gmail.com> (raw)
In-Reply-To: <18707.1098477434@sss.pgh.pa.us>
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
>
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: icub3d@gmail.com
Subject: Re: Slow query
In-Reply-To: <38242de9041023050832a57cbf@mail.gmail.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