Re: delete query taking way too long

On Thu, 12 Aug 2010 12:50:49 +0100
Thom Brown <thom(at)linux(dot)com> wrote:

> On 12 August 2010 12:14, Ivan Sergio Borgonovo
> <mail(at)webthatworks(dot)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:

ItemID is a PK.
Even if there is no constraint on Articoli_delete.id they *should*
be unique.

There are some other tables with indexes... but I don't expect that
more than 4K row for each table will be deleted.
There are a couple of other large (1M rows) table where an on delete
cascade is defined.

This is the query plan
Nested Loop (cost=30.07..10757.29 rows=1766 width=6)
-> HashAggregate (cost=30.07..47.73 rows=1766 width=8)
-> Seq Scan on articoli_delete (cost=0.00..25.66 rows=1766
width=8)
-> Index Scan using catalog_items_pkey on catalog_items
(cost=0.00..6.05 rows=1 width=14)
Index Cond: (catalog_items.itemid = articoli_delete.id)

BTW it is happening again... after I stopped pg, restarted the whole
server and re-run the query.

This query get generally unnoticed in a longer process but I doubt
it ever lasted more than a couple of minutes in the past.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it