删除chunks PostgreSQL中的大量行

Question

我有一个超过7700万行的表。其中大部分(大约5000万)我必须去除。我创建了删除行块的函数：

CREATE OR REPLACE FUNCTION cleanupItems(skipRowsNumber BIGINT) RETURNS BIGINT AS $$
DECLARE
    cur_ids CURSOR FOR SELECT id, event_id FROM someTable ORDER BY id limit 1000 offset skipRowsNumber;
    row RECORD;
    processed_count BIGINT;
BEGIN
    processed_count = 0;
    OPEN cur_ids;
    LOOP
        FETCH cur_ids INTO row;

        IF row is null THEN
            exit;
        end if;

        delete from items
        where ref_id = row.id
          and event_id <> row.event_id;

        processed_count = processed_count + 1;
    END LOOP;
    CLOSE cur_ids;

    RETURN processed_count;
END $$ LANGUAGE plpgsql;

然后我从outter循环中调用这个函数：

DO $$
DECLARE
    processed_count BIGINT;
    offsetRows BIGINT;
BEGIN
    offsetRows = 0;
    LOOP
        raise notice 'removing items starting from %', offsetRows;
        processed_count = cleanupItems(offsetRows);
        offsetRows = offsetRows + processed_count;
        EXIT WHEN processed_count = 0;
    END LOOP;
END $$;

然而，这是非常慢的(需要1个多小时)。在一开始，它运行得很快，但是删除了更多的行，调用变慢了。

有没有办法加快速度呢？

Answer 1

不如清空并重新填充表呢？我想这就是你的逻辑：

create table temp_items as
    select i.*
    from items i 
    where exists (select 1 
                  from sometable s
                  where i.ref_id = s.id and i.event_id <> s.event_id
                 );

truncate table items;   -- back it up first!

insert into items
    select * from temp_items;

对于第一个查询，您需要一个sometable(id, event_id)上的索引。

Answer 2

随着偏移量的增加，循环变得越来越慢，这一事实恰好描述了您对循环的期望。Postgres没有办法有效地实现这种偏移量，因为据它所知，一些其他会话正在添加、删除或更新行，这将影响偏移量计算。所以要做LIMIT 1000偏移量65000，它必须处理66000行，忽略其中的65000行，并实际使用其中的1000行。跳过所有这些行所需的总时间将与someTable中行数的平方成正比(您没有告诉我们)。

如果someTable.id是唯一的，那么您可以记录并将看到的someTable.id的最高值传递回外部DO，它可以在下一次循环中再次传递该值。然后你就可以把光标变成：

cur_ids CURSOR FOR SELECT id, event_id FROM someTable 
    where id>lastSeenId 
    ORDER BY id limit 1000;

或者，如果这不方便，您可以增加限制的值。在某一时刻，应用于N^2项的常量因子将足够小，以至于实际删除将比重复跳过行的开销慢。