[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
Re: [PERFORMANCE] slow small delete on large table Re: [PERFORMANCE] slow small delete on large table |
Список | 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 по дате отправления: