--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> > UPDATE T1
> > SET T1.title = T2.title
> > FROM T2
> > WHERE T1.id = T2.id
> > AND T1.title IS NULL;
>
> Thanks much RIchard, but neither of those work. For me table t1 has
> over 6 million rows, and table t2 has about 600,000. In both of the
> queries above I suppose it is going through each and every row of
> table t1 and taking its own sweet time. I've dropped all indexes on
> t1, but the query has still been running for over 45 minutes as I
> write! Any other suggestions?
You could post the explain plan for this above query to verify your theory. Also it might give
ideas about whether carefully placed indexs would help.
My guess is that you will need to add indexes at least on T1.id and T2.id to help reduce the join
time. Also, since you mention that you already attempted this query, I would view the
postgresql-logs to see if you might need to increase your check-point size temporarily just for
this operation.
Also, since this is hopefully a one time update, you could temporarily turn off fsync during the
update. when the update is done, you should turn fsync back on.
My guess is that a join and update on 6 million records in just going to take a while. Hopefully
this isn't an operation that you will need to preform regularly.
Regards,
Richard Broersma Jr.
