> 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)
1) Run the query with EXPLAIN, e.g. something like EXPLAIN DELETE FROM ...
This won't actually execute the query, it will just prepare an execution
plan and print it. That might show some problems with the query.
Post the output of explain to explain.depesz.com and then send link to the
forum (you could post the explain output here, but it's difficult to
read).
2) Run ANALYZE on the tables involved in the query and then the EXPLIAIN
again (this might show some problems with obsolete statistics).
3) Are there any tables depending on the "catalog_items" table? I mean are
there any foreign keys referencing it through a foreign key? How large are
those tables? Are the FK columns indexed?
regards
Tomas