Re: Updates, deletes and inserts are very slow. What can I do make them bearable? - Mailing list pgsql-general

Tim Uckun, 21.10.2010 07:05:
>> 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
>
> cu is an alias for consolidated_urls. tu is an alias for trending_urls.
>
> There are only two tables in the query.

Yes, but consolidated_urls is there twice. Which makes it three relations involved in the update
(consolidated_urls, cu and tu)

That's what Tom meant and that's where your cartesian product comes from.

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

That select is not the same as your UPDATE statement.

If your update statement was re-written to a plain SELECT it would be something like

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

See the difference?

Regards
Thomas