[PERFORMANCE] slow small delete on large table

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORMANCE] slow small delete on large table
Date: 2004-02-24 02:10:57
Message-ID: 200402231910.57078.pgsql@bluepolka.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Christopher Kings-Lynne 2004-02-24 02:34:00 Re: [PERFORMANCE] slow small delete on large table
Previous Message Markus Bertheau 2004-02-24 01:23:27 Re: Slow join using network address function