Обсуждение: DELETE running at snail-speed

I'm running a delete command on a postgresql-8.1.9 server. 25000
tuples are delete, time is ~ 400 secs

the command:
delete from downtime where start<'Aug 1 00:00:00 2008'


The table definitions are:
linie4=> \d downtime
                                         Table "public.downtime"
   Column   |            Type             |
Modifiers
------------+-----------------------------
+---------------------------------------------------------------
 downtimeid | integer                     | not null default nextval
('downtime_downtimeid_seq'::regclass)
 status     | smallint                    |
 start      | timestamp without time zone | default now()
 machineid  | smallint                    |
 a_nr       | integer                     |
Indexes:
    "downtime_pkey" PRIMARY KEY, btree (downtimeid)
    "idx_downtime_start" btree ("start")
Foreign-key constraints:
    "machineid_ok" FOREIGN KEY (machineid) REFERENCES machine
(machineid) ON UPDATE CASCADE ON DELETE CASCADE

linie4=> \d downtime_detail
                               Table "public.downtime_detail"
   Column   |   Type   |
Modifiers
------------+----------
+--------------------------------------------------------------------
 detailid   | integer  | not null default nextval
('downtime_detail_detailid_seq'::regclass)
 downtimeid | integer  |
 detail     | smallint |
Indexes:
    "downtime_detail_pkey" PRIMARY KEY, btree (detailid)
Foreign-key constraints:
    "statusid_ok" FOREIGN KEY (downtimeid) REFERENCES downtime
(downtimeid) ON UPDATE CASCADE ON DELETE CASCADE

I suspect the foreign key constraint of downtime_detail to slow down
the delete process. Is this a bug, probably fixed in latest version
(8.1.x) or should I drop the constraint and recreate after deletion -
which I only see as workaround ?

Gerhard

oops, should have thought about setting the foreign key constraint
deferrable, maybe showing this in table definition (\d) would be a
good idea.
will recreate my foreign key deferrable and do some timings ...

gerhard <g.hintermayer@inode.at> writes:
> oops, should have thought about setting the foreign key constraint
> deferrable, maybe showing this in table definition (\d) would be a
> good idea.

Don't think that'll make any difference.  What *should* help is having
an index on downtime_detail.downtimeid.  As-is it's got to seqscan
downtime_detail to look for rows to be cascade-deleted.

            regards, tom lane

On Thu, 2008-12-18 at 05:29 -0800, gerhard wrote:
> I suspect the foreign key constraint of downtime_detail to slow down
> the delete process. Is this a bug, probably fixed in latest version
> (8.1.x) or should I drop the constraint and recreate after deletion -
> which I only see as workaround ?

The foreign key is the cause indeed, but you should put an index on
downtime_detail(downtimeid) and it would work fine. What happens is that
for each row you delete from 'downtime' table, the following is done by
the foreign key triggers (cascade the deletion to the child tables):

delete from downtime_detail where downtimeid = $1

You can try to see what kind of plan you get for that by:

prepare test_001(integer) as delete from downtime_detail where
downtimeid = $1;

explain execute test_001(0);

Now multiply whatever you get there by the count of rows deleted from
'downtime' and you'll get the reason why it is slow... then try it again
with the above mentioned index in place.

Cheers,
Csaba.

On Thu, Dec 18, 2008 at 05:29:52AM -0800, gerhard wrote:
> I'm running a delete command on a postgresql-8.1.9 server. 25000
> tuples are delete, time is ~ 400 secs
>  detailid   | integer  | not null default nextval ('downtime_detail_detailid_seq'::regclass)
>  downtimeid | integer  |
>  detail     | smallint |
> Indexes:
>     "downtime_detail_pkey" PRIMARY KEY, btree (detailid)
> Foreign-key constraints:
>     "statusid_ok" FOREIGN KEY (downtimeid) REFERENCES downtime (downtimeid) ON UPDATE CASCADE ON DELETE CASCADE
>
> I suspect the foreign key constraint of downtime_detail to slow down
> the delete process.

Try adding an index on "downtime_detail.downtimeid".  If you have a lot
of entries in this table PG will spend a lot of time finding the entries
to delete them.

> Is this a bug, probably fixed in latest version
> (8.1.x) or should I drop the constraint and recreate after deletion -
> which I only see as workaround ?

It's not really a bug (although there probably is code that could be
written to make this case go faster) you can see where PG is actually
spending time by doing an EXPLAIN ANALYSE on the DELETE.  It should
display the time spent executing the triggers, but it's been a while
since I've used 8.1 so I'm not sure.


  Sam

On Dec 18, 2:46 pm, gerhard <g.hinterma...@inode.at> wrote:
> oops, should have thought about setting the foreign key constraint
> deferrable, maybe showing this in table definition (\d) would be a
> good idea.
> will recreate my foreign key deferrable and do some timings ...

OK, recreated my foreign key constraints deferrable (actually used in
2 tables) but still deleting at snail speed. :-(

Added indices on the two tables that used foreign keys to the delting
table, and voila - delete finishes fast.
Thanks for the tip, also using explain analyze, as someone else
pointed out, would have shown me, where the problem comes from.

Thanks everybody for helping.
Gerhard