This is based on a report from a user.
Index lookups become very slow after a bulk delete operation.
Steps to reproduce with a totally synthetic dataset:
The query (originally it was a join):
The query itself returns 100 rows in 0.01 sec.
Now, let's do the same in the transaction:
Re-running the query:
The EXPLAIN is the same, but the query now takes 1.25 sec.
(this is a reduced testcase. In the original report, the join query couldn't finish at all)