Tim Uckun <timuckun@gmail.com> writes:
> Here is the actual query.
> update consolidated_urls
> set screenshot_file_name = tu.screenshot_file_name,
> screenshot_content_type = tu.screenshot_content_type,
> screenshot_file_size = tu.screenshot_file_size,
> screenshot_status = tu.screenshot_status
> from consolidated_urls cu
> inner join trending_urls tu on tu.consolidated_url_id = cu.id
> This is a simple inner join.
No, it isn't. This is a three-way join between consolidated_urls, cu,
and tu --- the fact that cu is the same underlying table as
consolidated_urls doesn't change that. And the join is
underconstrained, causing each row of consolidated_urls to be joined
to every row of the cu/tu join. That's why it's taking such an
unreasonably long time --- you're generating many thousands of redundant
updates to each row of consolidated_urls. You should just write this as
update consolidated_urls
set screenshot_file_name = tu.screenshot_file_name,
screenshot_content_type = tu.screenshot_content_type,
screenshot_file_size = tu.screenshot_file_size,
screenshot_status = tu.screenshot_status
from trending_urls tu where tu.consolidated_url_id = consolidated_urls.id
Postgres is a bit different from some other DBMSes in how it interprets
UPDATE ... FROM syntax.
regards, tom lane