Re: DELETE running at snail-speed

Поиск
Список
Период
Сортировка
От Sam Mason
Тема Re: DELETE running at snail-speed
Дата
Msg-id 20081218143054.GE3008@frubble.xen.chris-lamb.co.uk
обсуждение исходный текст
Ответ на DELETE running at snail-speed  (gerhard <g.hintermayer@inode.at>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Csaba Nagy
Дата:
Сообщение: Re: DELETE running at snail-speed
Следующее
От: gerhard
Дата:
Сообщение: Re: DELETE running at snail-speed