Tim Uckun <timuckun@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
