EDIT: Based on some of my debugging and logging, I think the question boils down to why is DELETE FROM table WHERE id = x much faster than DELETE FROM table WHERE id IN (x) where x is just a single ID.
I recently tested batch-delete versus deleting each row one by one and noticed that batch-delete was much slower. The table had triggers for delete, update, and insert but I've tested with and without the triggers and each time batch-delete was slower. Can anyone shed some light on why this is the case or share tips on how I can debug this? From what I understand, I can't really reduce the number of times the trigger activates but I had originally figured that lowering the number of "delete" query would help with the performance.
I've included some information below, please let me know if I've left out anything relevant.
Deletion are done in batches of 10,000 and the code look something like :
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();
}
The code to delete just a single row is basically:
SessionFactory.getCurrentSession().delete(obj);
The table has two indexes which is not used in any of the deletion. No cascade operation will occur.
Here is a sample of the EXPLAIN ANALYZE of DELETE FROM table where 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,2,3}'::bigint[]))
-> Bitmap Index Scan on pk_table (cost=0.00..12.82 rows=3 width=0