Re: delete query taking way too long

On 12 August 2010 12:14, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:
> I've
> delete from catalog_items where ItemID in (select id from
> import.Articoli_delete);
>
> id and ItemID have an index.
>
> catalog_items is ~1M rows
> Articoli_delete is less than 2K rows.
>
> This query has been running for roughly 50min.
> Right now it is the only query running.
>
> PostgreSQL 8.3.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC)
> 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
>
> --
You can try to do deletes in batches of 10,000:

DELETE FROM catalog_items WHERE ItemID IN (SELECT id FROM
import.Articoli_delete LIMIT 10000);

But an EXPLAIN would tell us a lot more.

--
Thom Brown
Registered Linux user: #516935