From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Updates, deletes and inserts are very slow. What can I do make them bearable? |
Date: | 2010-10-21 04:10:18 |
Message-ID: | AANLkTinJHQA92OC5uVaLDeeLw7uTHUCPP=wtXD5K=_d4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Oct 21, 2010 at 5:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tim Uckun <timuckun(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Neil D'Souza | 2010-10-21 04:20:29 | a query on stored procedures/functions in pgsql |
Previous Message | Andrej | 2010-10-21 04:09:34 | Re: Cannot Start Postgres After System Boot |