Sure, maybe an annotation to add an index where needed would be great. Plans on including depend really if somebody implements it
The VAS allows to skip the subquery entirerly, so maybe it would have even better performance ... but that would require measuring, of course. Probablby depends on the DB as well.
Instead of an annotation, how about adding index to all columns referred to by relationships that will be "loaded"? Maybe another configuration property can be added to control whether or not these indices are generated so other users can turn it off.
VAS may indeed have better performance when querying, but with these indices, you dont get the extra SQL hit during DB writes. I agree that measuring will give us better insights on the actual differences.
In our use case, it is preferable take the hit when pulling audit records, since this is not done frequently and only by a few users, rather than during commits.
I do have some code that generates the indices but I dont think its in the best location. I wanted to code outside of the envers classes to minimize the impact during upgrades. Basically, I have a custom hibernate Configuration that intercepts calls to addDocument done by envers, and using AuditConfig metadata, I add the indexes on these Documents before passing it to the actual hibernate Configuration.
Property sounds good as well.
Nice hack with the addDocument