Thread: Delete performance on delete from table with inherited tables

Delete performance on delete from table with inherited tables

From

Chris Kratz

Date:

09 March 2004, 22:47:47

Hello all,

I have a performance issue that I cannot seem to solve and am hoping that
someone might be able to make some suggestions.

First some background information. We are using PostgreSQL 7.3.4 on Linux
with kernel 2.4.19. The box is a single P4 2.4Ghz proc with 1G ram and uw
scsi drives in a hardware raid setup.

We have a transactioninfo table with about 163k records. psql describes the
table as:

There are about 67 inherited tables that inherit the fields from this table,
hence the 134 constraint triggers.

There is a related table transactionlog which has a fk(foreign key) to
transactioninfo. It contains about 600k records.

There are 67 hist_tablename tables, each with a different structure. Then an
additional 67 tables called hist_tablename_log which inherit from the
transactionlog table and appropriate hist_tablename table. By the automagic
of inheritance, since the transactionlog has a fk to transactioninfo, each of
the hist_tablename_log tables does as well (if I am reading the pg_trigger
table correctly).

Once a day we run a sql select statement to clear out all records in
transactioninfo that don't have a matching record in transactionlog. We
accumulate between 5k-10k records a day that need clearing from
transactioninfo. That clear ran this morning for 5 hours and 45 minutes.

Today I am working on streamlining the sql to try and get the delete down to a
manageable time frame. The original delete statement was quite inefficent.
So, far, I've found that it appears to be much faster to break the task into
two pieces. The first is to update a flag on transactioninfo to mark empty
transactions and then a followup delete which clears based on that flag. The
update takes about a minute or so.

update only transactioninfo set del=TRUE where
not exists (select transactionid from transactionlog l where
l.transactionid=transactioninfo.transactionid);
UPDATE 6911
Time: 59763.26 ms

Now if I delete a single transactioninfo record found by selecting del=true
limit 1 I get

explain analyze delete from only transactioninfo where transactionid=734607;
QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
Index Scan using transactioninfo_pkey on transactioninfo (cost=0.00..6.01
rows=1 width=6) (actual time=0.18..0.18 rows=1 loops=1)
Index Cond: (transactionid = 734607)
Total runtime: 0.41 msec
(3 rows)

Time: 855.08 ms

With the 7000 records to delete and a delete time of 0.855s, we are looking at
1.5hrs to do the clear which is a great improvement from the 6 hours we have
been seeing. But it still seems like it should run faster.

The actual clear statement used in the clear is as follows:
explain delete from transactioninfo where del=true;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on transactioninfo (cost=0.00..6177.21 rows=78528 width=6)
Filter: (del = true)
(2 rows)

Another interesting observation is that the raid subsystem shows very low
activity during the clear. The backend process is almost entirely cpu bound.

Some of the documentation implies that inherited tables cause deletes to be
very slow on the parent table, so I did the following experiment.

vistashare=# create table transactioninfo_copy as select * from
transactioninfo;
SELECT
Time: 6876.88 ms
vistashare=# create index transinfo_copy_del_idx on transactioninfo_copy(del);
CREATE INDEX
Time: 446.20 ms
vistashare=# delete from transactioninfo_copy where del=true;
DELETE 6904
Time: 202.33 ms

Which certainly points to the triggers being the culprit. In reading the
documentation, it seems like the "delete from only..." statement should
ignore the constraint triggers. But it seems quite obvious from the
experiments that it is not. Also, the fact that the query plan doesn't show
the actual time used when analyze is used seems to again point to the after
delete triggers as being the culprit.

Is there any other way to make this faster then to drop and rebuild all the
attached constraints? Is there a way to "disable" the constraints for a
single statement. Because of the unique nature of the data, we know that the
inherited tables don't need to be inspected. The table structure has worked
quite well up till now and we are hoping to not have to drop our foreign keys
and inheritance if possible. Any ideas?

Thanks for your time,

-Chris
--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC

Re: Delete performance on delete from table with inherited

From

Stephan Szabo

Date:

09 March 2004, 23:46:51

On Wed, 3 Mar 2004, Chris Kratz wrote:

> Which certainly points to the triggers being the culprit.  In reading the
> documentation, it seems like the "delete from only..." statement should
> ignore the constraint triggers.  But it seems quite obvious from the

Delete from only merely means that children of the table being deleted
will not have their rows checked against any where conditions and removed
for that reason.  It does not affect constraint triggers at all.

Given I'm guessing it's going to be running about 7000 * 67 queries to
check the validity of the delete for 7000 rows each having 67 foreign
keys, I'm not sure there's much to do other than hack around the issue
right now.

If you're a superuser, you could temporarily hack reltriggers on the
table's pg_class row to 0, run the delete and then set it back to the
correct number. I'm guessing from your message that there's never any
chance of a concurrent transaction putting in a matching row in a way that
something is marked as deletable when it isn't?

Re: Delete performance on delete from table with inherited tables

From

Tom Lane

Date:

10 March 2004, 00:09:43

Chris Kratz <chris.kratz@vistashare.com> writes:
> There are about 67 inherited tables that inherit the fields from this table,
> hence the 134 constraint triggers.

Why "hence"?  Inheritance doesn't create any FK relationships.  You must
have done so.  What are those FK constraints exactly?

> Some of the documentation implies that inherited tables cause deletes to be
> very slow on the parent table, so I did the following experiment.

No, but foreign keys linked from tables that don't have indexes can be
pretty slow.

> it seems like the "delete from only..." statement should
> ignore the constraint triggers.

Why would you expect that?

It appears to me that this table is the referenced table for a large
number of foreign-key relationships, and thus when you delete a row from
it, many other tables have to be checked to verify that they do not
contain entries matching that row.  That's going to be relatively slow,
even with indexes on the other tables.  It's not very clear from your
description what the FK relationships actually do in your database
schema, but I would suggest looking at redesigning the schema so that
you do not need them.

            regards, tom lane

Re: Delete performance on delete from table with inherited tables

From

Chris Kratz

Date:

05 April 2004, 18:27:20

Thanks Stephan and Tom for your responses.  We have been busy, so I haven't
had time to do any further research on this till yesterday.  I found that the
large number of triggers on the parent or master table were foreign key
triggers for each table back to the child tables (update and delete on
master, insert on child).  The triggers have existed through several versions
of postgres and as far as we can tell were automatically created using the
references keyword at inception.

Yesterday I dropped all the current triggers on parent and children and ran a
script that did an alter table add foreign key constraint to each of the 67
child tables with update cascade delete cascade.  After this, the delete from
the parent where no records existed in the child tables was far more
acceptable.  Instead of taking hours to do the delete, the process ran for
about 5 minutes on my workstation.  Removing all constraints entirely reduces
this time to a couple of seconds.  I am currently evaluating if the foreign
key constraints are worth the performance penalty in this particular case.

To finish up, it appears that the foreign key implementation has changed since
when these first tables were created in our database.  Dropping the existing
triggers and re-adding the constraints on each table significantly improved
performance for us.  I do not know enough of the internals to know why this
happened.  But our experience seems to prove that the newer implementation of
foreign keys is more efficient then previous versions.  YMMV

One other item that was brought up was whether the child tables have the fk
column indexed, and the answer was yes.  Each had a standard btree index on
the foreign key.   Explain showed nothing as all the time was being spent in
the triggers.  Time spent in triggers is not shown in the pg 7.3.4 version of
explain (nor would I necessarily expect it to).

Thanks for your time, expertise and responses.

-Chris

On Tuesday 09 March 2004 7:18 pm, Stephan Szabo wrote:
> On Wed, 3 Mar 2004, Chris Kratz wrote:
> > Which certainly points to the triggers being the culprit.  In reading the
> > documentation, it seems like the "delete from only..." statement should
> > ignore the constraint triggers.  But it seems quite obvious from the
>
> Delete from only merely means that children of the table being deleted
> will not have their rows checked against any where conditions and removed
> for that reason.  It does not affect constraint triggers at all.
>
> Given I'm guessing it's going to be running about 7000 * 67 queries to
> check the validity of the delete for 7000 rows each having 67 foreign
> keys, I'm not sure there's much to do other than hack around the issue
> right now.
>
> If you're a superuser, you could temporarily hack reltriggers on the
> table's pg_class row to 0, run the delete and then set it back to the
> correct number. I'm guessing from your message that there's never any
> chance of a concurrent transaction putting in a matching row in a way that
> something is marked as deletable when it isn't?

--
Chris Kratz
Systems Analyst/Programmer
VistaShare LLC
www.vistashare.com