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

On Thu, Oct 21, 2010 at 5:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tim Uckun <timuckun@gmail.com> writes:
>> I have a very simple update query.
>
>> update cu
>> 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  cu
>> inner join tu on tu.cu_id = cu.id
>
> That isn't actually the query you're issuing, because if it were
> you would get an error "table name "cu" specified more than once".

Sorry I shortened the names of the tables.

> I suspect that the query you're actually issuing involves an
> unconstrained cartesian product self-join between the target table
> and another instance of itself.  Postgres doesn't consider that
> the target table should be named again in FROM.  But it's hard to
> be sure about that when looking at a redacted query.

I apologize for the redacted query. I was trying to make it easier to follow.

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.

select count(cu.id)
from  consolidated_urls cu
inner join trending_urls tu on tu.consolidated_url_id = cu.id

yields 3657 records.