Delete very slow after deletion of many rows in dependent table

[Cornelius Buschka]

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: [phone]

http://www.arcusx.com c.buschka AT arcusx DOT com
__________________________________________________ ______


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Cornelius Buschka]

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: [phone]

http://www.arcusx.com mailto:c.*******@arcusx.com
__________________________________________________ ______
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)