代码之家 › 专栏 › 技术社区 › alamar

删除包含引用同一表的列的行需要耗费大量时间

constraints indexing postgresql performance

alamar · 技术社区 · 17 年前

很抱歉问了一个非常具体的问题。

parent_block_id :这是初级的。

我尝试添加了不同的索引:只是(parent_block_id);其中parent_block_id=0;其中parent_block_id为空;在哪里parent_block_id!= 0.这两项都没有带来严重的性能提升。

varshavka=> explain analyze delete from infoblocks where template_id = 112;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on infoblocks  (cost=0.00..1234.29 rows=9 width=6) (actual time=13.271..40.888 rows=40000 loops=1)
   Filter: (template_id = 112)
 Trigger for constraint $1: time=4051.219 calls=40000
 Trigger for constraint $2: time=1616.194 calls=40000
 Trigger for constraint cs_ibrs: time=2810.144 calls=40000
 Trigger for constraint cs_ibct: time=4026.305 calls=40000
 Trigger for constraint cs_ibbs: time=3517.640 calls=40000
 Trigger for constraint cs_ibreq: time=774344.010 calls=40000
 Total runtime: 790760.168 ms
(9 rows)



varshavka=> \d infoblocks
                                      Table "public.infoblocks"
     Column      |            Type             |                      Modifiers
-----------------+-----------------------------+------------------------------------------------------
 id              | integer                     | not null default nextval(('IB_SEQ'::text)::regclass)
 parent_block_id | integer                     |
 nm_id           | integer                     | default 0
 template_id     | integer                     | not null
 author_id       | integer                     |
 birthdate       | timestamp without time zone | not null
Indexes:
    "infoblocks_pkey" PRIMARY KEY, btree (id)
    "zeroparent" btree (parent_block_id) WHERE parent_block_id <> 0
Foreign-key constraints:
    "$2" FOREIGN KEY (nm_id) REFERENCES newsmakers(nm_id) ON DELETE RESTRICT
    "$5" FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE RESTRICT
    "cs_ibreq" FOREIGN KEY (parent_block_id) REFERENCES infoblocks(id) ON DELETE CASCADE

3 回复 | 直到 11 年前

Community Mohan Dere 9 年前

VACUUM ANALYZE d最近。

araqnid's answer 。但是,如果您需要一些在未来某些行具有非零、非null时仍能继续工作的东西 parent_block_id

我猜PostgreSQL不会合并由以下原因引起的删除 ON DELETE CASCADE EXPLAIN parent_block_id

所以,你可能会得到一个很大的加速通过改变 删除时级联 ON DELETE RESTRICT ,并手动编译一个临时表中需要执行的所有删除的列表,然后一次全部删除。 如果层次结构的最大深度很小,这种方法将非常快。 以下是一些伪代码:

# Insert the top-level rows as "seed" rows.
INSERT INTO rows_to_delete
    SELECT id, 0 FROM infoblocks WHERE template_id = 112

# Gather all rows that are children of any row at depth curLevel,
# advancing curLevel until no more children are found.
curLevel = 0
while (nRowsReturnedFromLastInsert > 0) {
    INSERT INTO rows_to_delete
        SELECT ib.id, rtd.level + 1
        FROM infoblocks ib
        JOIN rows_to_delete rtd ON (ib.parent_block_id = rtd.id)
        WHERE rtd.level = curLevel

    curLevel = curLevel + 1
}

DELETE FROM infoblocks
    JOIN rows_to_delete rtd ON (infoblocks.id = rtd.id)

ON DELETE NO ACTION 关于删除限制 DELETE 成功——我不清楚一个人是否在以下情况下,允许语句删除父级及其所有子级 关于删除限制 实际上。如果出于某种原因这是不可接受的,你总是可以遍历多个 删除

araqnid 17 年前

如果你能暂时屏蔽其他人,也许可以放弃约束 cs_ibreq ,删除约束,然后重新添加约束?

parent_block_id

Mosty Mostacho 14 年前

template_id