| 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: | Whole Thread | Raw Message | 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.
| 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 |