I changed the envers-console-demo to show the generated SQL and ran it again.

Initial Person data:

id = 1, name = James, surname = Bond, address = <1> MI6 18/25
id = 2, name = John, surname = McClane, address = <2> Nakatomi Plaza 10/34
id = 3, name = Holly, surname = Gennaro, address = <2> Nakatomi Plaza 10/34

So Mr. Bond is with MI6, I go ahead and edit the address 3 times.

Address History:

revision = 1, id = 1, streetName = MI6, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:13:22 EDT 2009)
revision = 2, id = 1, streetName = 14th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:41 EDT 2009)
revision = 3, id = 1, streetName = 15th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:50 EDT 2009)
revision = 4, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:15:05 EDT 2009)

List Persons:

id = 1, name = James, surname = Bond, address = <1> 16th Street 18/25
id = 2, name = John, surname = McClane, address = <2> Nakatomi Plaza 10/34
id = 3, name = Holly, surname = Gennaro, address = <2> Nakatomi Plaza 10/34

When I search for History of Mr. Bond, I get this; The address corresponding to Mr. Bond at that revision is still MI6.

revision = 1, id = 1, name = James, surname = Bond, address = <1> MI6 18/25 (Thu Jul 23 15:13:22 EDT 2009)

I make John stay at the same address as Mr. Bond

revision = 1, id = 1, streetName = MI6, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:13:22 EDT 2009)
revision = 2, id = 1, streetName = 14th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:41 EDT 2009)
revision = 3, id = 1, streetName = 15th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:14:50 EDT 2009)
revision = 4, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond) (Thu Jul 23 15:15:05 EDT 2009)
revision = 6, id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<2> John McClane, <1> Thames Bond) (Thu Jul 23 15:30:26 EDT 2009)

Went ahead and searched for the Address at that revision, for which I provide the following info;

-----------------------------------------------
1 - list persons 5 - list addresses
2 - list person history 6 - list addresses history
3 - new person 7 - new address
4 - modify person 8 - modify address
9 - get person at revision 10 - get address at revision
 0 - end
10
Address id: 1
Revision number: 4
id = 1, streetName = 16th Street, houseNumber = 18, flatNumber = 25, persons = (<1> James Bond)

and I get this SQL generated.

Hibernate: -----------> {A}
 select
 address_ve0_.id as id3_,
 address_ve0_._revision as column2_3_,
 address_ve0_._revision_type as column3_3_,
 address_ve0_.flatNumber as flatNumber3_,
 address_ve0_.houseNumber as houseNum5_3_,
 address_ve0_.streetName as streetName3_
 from
 Address_versions address_ve0_
 where
 address_ve0_._revision_type<>?
 and (
 address_ve0_._revision in (
 select
 max(address_ve1_._revision)
 from
 Address_versions address_ve1_
 where
 address_ve1_._revision<=?
 and address_ve0_.id=address_ve1_.id
 )
 )
 and address_ve0_.id=?
Hibernate: -----------> {B}
 select
 person_ver0_.id as col_0_0_,
 person_ver0_._revision as col_0_1_
 from
 Person_versions person_ver0_
 where
 person_ver0_.address_id=?
 and (
 person_ver0_._revision in (
 select
 max(person_ver1_._revision)
 from
 Person_versions person_ver1_
 where
 person_ver1_._revision<=?
 and person_ver0_.id=person_ver1_.id
 )
 )
 and person_ver0_._revision_type<>?

I can understand the {B} part of the code, where we need to find the Person using the address at that version, but for {A}, since we know the "exact" version of the address, can we avoid the subselect

and (
 address_ve0_._revision in (
 select
 max(address_ve1_._revision)
 from
 Address_versions address_ve1_
 where
 address_ve1_._revision<=?
 and address_ve0_.id=address_ve1_.id
 )

Will be a nice performance improvement.