From: | Joshua Marsh <icub3d(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow query |
Date: | 2004-10-23 12:08:04 |
Message-ID: | 38242de9041023050832a57cbf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bjorn Bength | 2004-10-23 15:54:05 | different io elevators in linux |
Previous Message | Steinar H. Gunderson | 2004-10-23 11:15:30 | Re: Insert performance, what should I expect? |