[PERFORMANCE] slow small delete on large table

Поиск
Список
Период
Сортировка
От Ed L.
Тема [PERFORMANCE] slow small delete on large table
Дата
Msg-id 200402231910.57078.pgsql@bluepolka.net
обсуждение исходный текст
Ответы Re: [PERFORMANCE] slow small delete on large table  (Christopher Kings-Lynne <chriskl@familyhealth.com.au>)
Re: [PERFORMANCE] slow small delete on large table  (Bruno Wolff III <bruno@wolff.to>)
Re: [PERFORMANCE] slow small delete on large table  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
A 7.3.4 question...

I want to "expire" some data after 90 days, but not delete too
much at once so as not to overwhelm a system with precariously
balanced disk I/O and on a table with millions of rows.  If I
could say it the way I think for a simple example, it'd be
like this:

    delete from mytable
    where posteddatetime < now() - '90 days'
    limit 100;

Of course, that's not legal 7.3.4 syntax.  These are both too
slow due to sequential scan of table:

    delete from mytable where key in (
        select key
        from mytable
        where posteddatetime < now() - '90 days'
        limit 100);
or
    delete from mytable where exists (
        select m.key
        from mytable m
        where m.key = mytable.key
          and m.posteddatetime < now() - '90 days'
        limit 100);

Tried to use a cursor, but couldn't figure out the syntax
for select-for-delete yet, or find appropriate example on
google.  Any clues?

TIA.


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

Предыдущее
От: Markus Bertheau
Дата:
Сообщение: Re: Slow join using network address function
Следующее
От: Christopher Kings-Lynne
Дата:
Сообщение: Re: [PERFORMANCE] slow small delete on large table