编辑：根据我的一些调试和日志记录,我认为这个问题归结为为什么DELETE FROM table WHERE id = x比DELETE FROM table WHERE id IN(x)要快得多,其中x只是一个ID.

我最近测试了批次删除,而不是逐个删除每一行,并注意到批次删除要慢得多.该表具有删除,更新和插入的触发器,但是我已经测试过和不使用触发器,每次批次删除更慢.任何人都可以看出为什么会这样的情况或分享关于我如何调试这个的提示？从我的理解,我不能真正减少触发器激活的次数,但我原来认为减少“删除”查询的数量将有助于表现.

我在下面列出了一些信息,如果我没有任何相关的内容,请告诉我.

删除是分批完成的,代码如下：

private void batchDeletion( Collection<Long> ids ) {
  StringBuilder sb = new StringBuilder();
  sb.append( "DELETE FROM ObjImpl WHERE id IN (:ids)" );

  Query sql = getSession().createQuery( sb.toString() );
  sql.setParameterList( "ids",ids );

  sql.executeUpdate();
}

基本上只删除一行的代码：

SessionFactory.getCurrentSession().delete(obj);

该表具有两个不用于任何删除的索引.不会发生级联操作.

以下是DELETE FROM表的EXPLAIN ANALYZE的示例,其中id IN(1,2,3);：

Delete on table  (cost=12.82..24.68 rows=3 width=6) (actual time=0.143..0.143 rows=0 loops=1)
  ->  Bitmap Heap Scan on table  (cost=12.82..24.68 rows=3 width=6) (actual time=0.138..0.138 rows=0 loops=1)
        Recheck Cond: (id = ANY ('{1,3}'::bigint[]))
        ->  Bitmap Index Scan on pk_table  (cost=0.00..12.82 rows=3 width=0) (actual time=0.114..0.114 rows=0 loops=1)
              Index Cond: (id = ANY ('{1,3}'::bigint[]))
Total runtime: 3.926 ms

每次重新加载数据进行测试时,我已经抽真空并重新建立索引,我的测试数据包含386,660行.

测试是删除所有行,我没有使用TRUNCATE,因为通常有一个选择标准,但为了测试目的,我已经使标准包括所有行.启用触发器后,逐一删除每行193,616ms,而批次删除则占用285,558ms.然后我禁用了触发器,并获得93,793ms的单行删除和181,537ms批量删除.触发器总结值并更新另一个表 – 基本上是簿记.

我已经玩了较少的批量(100和1),他们似乎表现更糟.

编辑：打开Hibernate日志记录和单行一行删除,它基本上是这样做：从表中删除id =？和解释分析：

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.042..0.042 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.037..0.037 rows=0 loops=1)
        Index Cond: (id = 3874904)
Total runtime: 0.130 ms

编辑：好奇,如果列表实际上包含10,000个ID,如果Postgres会做不同的事情：没有.

Delete on table  (cost=6842.01..138509.15 rows=9872 width=6) (actual time=17.170..17.170 rows=0 loops=1)
  ->  Bitmap Heap Scan on table  (cost=6842.01..138509.15 rows=9872 width=6) (actual time=17.160..17.160 rows=0 loops=1)
        Recheck Cond: (id = ANY ('{NUMBERS 1 THROUGH 10,000}'::bigint[]))
        ->  Bitmap Index Scan on pk_table  (cost=0.00..6839.54 rows=9872 width=0) (actual time=17.139..17.139 rows=0 loops=1)
              Index Cond: (id = ANY ('{NUMBERS 1 THROUGH 10,000}'::bigint[]))
Total runtime: 17.391 ms

编辑：基于上述的EXPLAIN ANALYZE,我从实际的删除操作中检索了一些记录.以下是单行逐行删除的两个变体的记录.

以下是一些单独的删除：

2013-03-14 13:09:25,424:delete from table where id=?
2013-03-14 13:09:25,424:delete from table where id=?

这是单个删除的其他变体(列表只是1项)

2013-03-14 13:49:59,858:delete from table where id in (?)
2013-03-14 13:50:01,460:delete from table where id in (?)
2013-03-14 13:50:03,040:delete from table where id in (?)
2013-03-14 13:50:04,544:delete from table where id in (?)
2013-03-14 13:50:06,125:delete from table where id in (?)
2013-03-14 13:50:07,707:delete from table where id in (?)
2013-03-14 13:50:09,275:delete from table where id in (?)
2013-03-14 13:50:10,833:delete from table where id in (?)
2013-03-14 13:50:12,369:delete from table where id in (?)
2013-03-14 13:50:13,873:delete from table where id in (?)

两者都是存在于表中的ID,应该是顺序的.

DELETE的EXPLAIN ANALYZE FROM table WHERE id = 3774887;

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.097..0.097 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.055..0.058 rows=1 loops=1)
        Index Cond: (id = 3774887)
Total runtime: 0.162 ms

DELETE的EXPLAIN ANALYZE FROM TABLE WHERE id IN(3774887);

Delete on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.279..0.279 rows=0 loops=1)
  ->  Index Scan using pk_table on table  (cost=0.00..8.31 rows=1 width=6) (actual time=0.210..0.213 rows=1 loops=1)
        Index Cond: (id = 3774887)
Total runtime: 0.452 ms

0.162 vs 0.452认为有显着性差异

编辑：

将批量大小设置为50,000,而Hibernate不喜欢这样的想法：

java.lang.StackOverflowError
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:40)
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:41)
        at org.hibernate.hql.ast.util.NodeTraverser.visitDepthFirst(NodeTraverser.java:42)
....

WHERE id = 1;

WHERE id IN (1);

WHERE id = ANY(ARRAY[1]);