Delete very slow after deletion of many rows in dependent table - Mailing list pgsql-general

Hi,

we saw the following problem:

We deleted all rows from a table B referencing table A (~500000 records). No
problem, but the following try to delete all records from table A (~180000) lead
to a "never ending" statement. We found out, that vacuuming table B after delete
did the trick.

It seems to us the database has to do scan thru deleted records on B while
deleting from A. Why did it last so long? An index on B.a_fk did not lead to
imporvements. The query plan did not help.

Could anybody explain the problem to me? Does anybody know a different solution
than vacuuming the table after big deletes? (We already vaccuum the database
periodically.)

Below the statements to reproduce the problem.

Regards
Cornelius

============================================================
-- create two tables, b references a through a_fk
create table table_a ( a_pk int8 primary key );

create table table_b ( b_pk int8 primary key, a_fk int8 not null references
table_a ( a_pk ) );

-- fill a and b with 100000 records
create or replace function fill_table_a() returns int8 as '
begin
  for i in 1..100000 loop
    insert into table_a values ( i );
  end loop;
  return 1;
end' language plpgsql;

select fill_table_a();

insert into table_b ( select a_pk, a_pk from table_a );

commit;

-- delete records from b, so records from a can be also be deleted
delete from table_b;
commit;

-- delete records from a;
-- this delete needs a VERY long time
delete from table_a;


-- we do it again, but vacuum table_b first

-- delete records from b, so records from a can be also be deleted
vacuum table_b;

-- delete records from a;
delete from table_a;
commit;

--
________________________________________________________

  Cornelius Buschka

  arcus(x) GmbH
  Hein-Hoyer-Straße 75     fon: +49 (0)40.333 102 92
  D-20359 Hamburg          fax: +49 (0)40.333 102 93

  http://www.arcusx.com    c.buschka AT arcusx DOT com
________________________________________________________