Обсуждение: Delete very slow after deletion of many rows in dependent table

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
________________________________________________________

On Sun, 21 Nov 2004, Cornelius Buschka wrote:

> 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.

An index seems to help for me.  It's still kinda slow, but the real time
for the delete on A goes from more minutes than I was willing to wait to
about 19s.

However, if you'd already run the key without the index, refilled the
table, made the index and tried it again, it probably wouldn't have used
the index because it tries to cache the plan on first use in each session
(you'd need to start a new session to try again).

Hi Stephan,

caching of the execution plan is a good hint. We'll try it in a new connection.

Best Regards
Cornelius

Stephan Szabo wrote:

> On Sun, 21 Nov 2004, Cornelius Buschka wrote:
>
>
>>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.
>
>
> An index seems to help for me.  It's still kinda slow, but the real time
> for the delete on A goes from more minutes than I was willing to wait to
> about 19s.
>
> However, if you'd already run the key without the index, refilled the
> table, made the index and tried it again, it probably wouldn't have used
> the index because it tries to cache the plan on first use in each session
> (you'd need to start a new session to try again).
>
>


--
________________________________________________________

  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    mailto:c.buschka@arcusx.com
________________________________________________________