请推荐最佳的批量删除选项 [英] Please recommend the best bulk-delete option

问题描述

我正在使用PostgreSQL 8.1.4。我有3个表：一个是核心表（table1），其他是从属表（table2，table3）。我在table1中插入了70000条记录，在其他2个表中插入了适当的相关记录。使用CASCADE时，我可以使用DELETE FROM table1删除相关记录；当我当前的PostgreSQL版本中的记录最少时，它可以正常工作。当我有大量记录时，它会尝试删除所有记录，但是许多小时都没有删除进度的迹象！而批量导入则在几分钟内完成。我希望在合理的时间内批量删除。我也尝试过TRUNCATE。像TRUNCATE table3，table2，table1;性能没有变化。这只需要更多时间，并且没有完成的迹象！从网上，我几乎没有其他选择，例如删除所有约束，然后重新创建约束。但是，当它加载更多数据时，似乎没有查询可以在 table1上成功运行！
请向我推荐最好的解决方案，在几分钟之内删除所有记录。

I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1), others are dependents (table2,table3). I inserted 70000 records in table1 and appropriate related records in other 2 tables. As I'd used CASCADE, I could able to delete the related records using DELETE FROM table1; It works fine when the records are minimal in my current PostgreSQL version. When I've a huge volume of records, it tries to delete all but there is no sign of deletion progress for many hours! Whereas, bulk import, does in few minutes. I wish to do bulk-delete in reasonable minutes. I tried TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in performance though. It just takes more time, and no sign of completion! From the net, I got few options, like, deleting all constraints and then recreating the same would be fine. But, no query seems to be successfully run over 'table1' when it's loaded more data! Please recommend me the best solutions to delete all the records in minutes.

CREATE TABLE table1(
        t1_id   SERIAL PRIMARY KEY,
        disp_name       TEXT NOT NULL DEFAULT '',
        last_updated TIMESTAMP NOT NULL DEFAULT current_timestamp,
        UNIQUE(disp_name)
    ) WITHOUT OIDS;

CREATE UNIQUE INDEX disp_name_index on table1(upper(disp_name));

CREATE TABLE table2 (
        t2_id           SERIAL PRIMARY KEY,
        t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
        type    TEXT
    ) WITHOUT OIDS;

CREATE TABLE table3 (
        t3_id           SERIAL PRIMARY KEY,
        t1_id   INTEGER REFERENCES table1 ON DELETE CASCADE,
        config_key      TEXT,
        config_value    TEXT
    ) WITHOUT OIDS;

致谢，
Siva。

Regards, Siva.

推荐答案

您可以在引用父表的子表的列上创建索引：

You can create an index on the columns on the child tables which reference the parent table:

在table2上创建索引在table3的t1_id列上

on table2 create an index on the t1_id column

在t1_id列上创建索引

on table3 create an index on the t1_id column

应该可以稍微加快速度。

that should speed things up slightly.

和/或，不要为删除级联中的烦恼，创建一个删除存储过程，该过程首先删除从子表再到父表，可能要比让postgresql为您做更快。

And/or, don't bother with the on delete cascade, make a delete stored procedure which deletes first from the child tables and then from the parent table, it may be faster than letting postgresql do it for you.

这篇关于请推荐最佳的批量删除选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！