Re: Updates, deletes and inserts are very slow. What can I do make them bearable?

Tim Uckun <timuckun(at)gmail(dot)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