cubicweb #1424495 DELETE is sluggish when lots of entities are deleted [resolved]

I believe I'm not the first one to be hit by the dreadful performance when massive deletion are performed in CW. The current example I have at hand involves deleting ~2000 entities, which will through composite relations cause the cascade deletion of ~45000 entities. The first DELETE statement takes ~300s on my computer and the commit (during which the 45k related entities are deleted) takes ages.

Here are some figures. The "delete" lines give the name of the etype, the number deleted and the time requires to execute the DELETE RQL query. The "commit" lines give the time for the commit statements. At the end, we have the output of the time shell builtin for the script.

The second commit deletes an additional 1512 YearRangeProfileBox entities and 44321 TimeSeries entities.

without patch:

delete ScenarioSet 1 0.135946989059
commit 0.0686221122742
delete RiskFactor 108 2.40645980835
delete Scenario 344 68.0118050575
delete SubYearlyBoxAssemblage 72 4.89098191261
delete TSBox 1894 274.777546167
commit 4313.74800014

real    77m49.850s
user    66m50.287s
sys     0m56.360s
prioritynormal
typeenhancement
done in3.10.8
load3.000
load left0.000
closed by<not specified>