Delete performance on delete from table with inherited tables - Mailing list pgsql-performance

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:

\d transactioninfo
                                  Table "public.transactioninfo"
    Column     |           Type           |                       Modifiers
---------------+--------------------------+--------------------------------------------------------
 transactionid | integer                  | not null default
nextval('transaction_sequence'::text)
 userid        | integer                  |
 programid     | integer                  |
 time          | timestamp with time zone |
 comment       | text                     |
 undoable      | boolean                  |
 del           | boolean                  |
Indexes: transactioninfo_pkey primary key btree (transactionid),
         delidx btree (del),
         transactioninfo_date btree ("time", programid, userid)
Triggers: RI_ConstraintTrigger_6672989,
          RI_ConstraintTrigger_6672990,
          RI_ConstraintTrigger_6672992,
--snip--
--snip--
          RI_ConstraintTrigger_6673121,
          RI_ConstraintTrigger_6673122

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