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

删除具有引用同一表的列的行需要相当长的时间。

constraints indexing postgresql performance

alamar · 技术社区 · 15 年前

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

我有一个表(见底部),当我试图从中删除很多记录时,我的PostgreSQL 8.2.5将98%的时间用于父子约束。我正试图弄清楚应该添加什么索引来加快速度。我不得不说,此表中的所有列都有0或空作为 parent_block_id :这是初步的。

我尝试添加不同的索引:只(父块\u id);父块\u id=0;父块\u id为空;父块\u 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 回复 | 直到 9 年前

Community CDub 7 年前

首先:第一个(零位!)当注意到难看的查询时间时,您应该做的事情是确保 VACUUM ANALYZE 最近。

如果只需要一次性删除,请参见 araqnid's answer . 但如果您需要一些在将来当某些行具有非零、非空值时仍能继续工作的内容 parent_block_id 字段,继续读取。

我猜PostgreSQL没有将 ON DELETE CASCADE 在一个查询中——事实上 EXPLAIN 输出将这些显示为触发器建议每个子行删除实际上将分别执行。大概每一行都将使用索引查找 父系阻断 但这仍然比在桌子上进行一次扫描慢得多。

所以,你可以通过改变 删除层叠时 到 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 15 年前

如果你能暂时阻止其他人,也许可以取消限制。 cs_ibreq ,执行删除操作,然后重新添加约束?

可能是因为只有一个非空值 parent_block_id ,检查约束时不使用索引?虽然这看起来有点奇怪。

Mosty Mostacho 13 年前

是否尝试将索引添加到 template_id ?