How to improve performance in Hibernate polymorphic query when using table per class strategy?

Question

When using table per class strategy, polymorphic query looks like this:

SELECT <some cols> 
FROM 
  (
    SELECT 
      parent_id,
      entity1_col,
      entity2_col,
      1 as clazz_ 
    FROM 
      entity_table1 
    UNION 
    SELECT 
      parent_id,
      entity1_col,
      entity2_col,
      2 as clazz_ 
    FROM 
      entity_table2 
  ) abstract_entity 
WHERE 
  abstract_entity.parent_id = X

We can see that if we have a lot of rows in entity_table1 and entity_table2, this query can lead to performance issue.

I am using Spring-data & Hibernate and this request is generated when fetching a OneToMany relation on a parent entity.

My question is : why Hibernate generates such request though it is restricting selected rows on parent_id that is common to entity_table1 & entity_table2 ?

Is there a way to have Hibernate generating a SQL request that look like this:

SELECT <some cols>
FROM 
  (
    SELECT 
      parent_id,
      entity1_col,
      entity2_col,
      1 as clazz_ 
    FROM 
      entity_table1 
    WHERE 
      parent_id=X
    UNION 
    SELECT 
      parent_id,
      entity1_col,
      entity2_col,
      2 as clazz_ 
    FROM 
      entity_table2 
    WHERE 
      parent_id=X
  ) abstract_entity 

Having "WHERE parent_id=X" in each request in FROM ? This way no performance issue when fetching relation on parent entity.

Thanks !

Answer 1

I made a modification based on hibernate 5.6.3 to pushdown the predict for polymorphic query when using table-per-concrete-class strategy. Please refer to https://github.com/kerler/hibernate-orm/tree/main_5.6.3-open-care.

After modification, for code “Query q = s.createQuery( "from Being h where h.identity = :name1 or h.identity = :name2 or h.identity = :name2" );” in hibernate UT case UnionSubclassTest.java::testNestedUnionedSubclasses(), the SQL generated by hibernate is like:

00:37:43,945 DEBUG SQL:144 - 
    select
        being0_.bid as bid1_2_,
        being0_.ident as ident2_2_,
        being0_.location as location3_2_,
        being0_.sex as sex1_6_,
        being0_.salary as salary1_4_,
        being0_.species as species1_0_,
        being0_.hive as hive2_0_,
        being0_.clazz_ as clazz_ 
    from
        ( select
            bid,
            ident,
            location,
            sex,
            salary,
            null as species,
            null as hive,
            2 as clazz_ 
        from
            employees 
        where
            ident=? 
            or ident=? 
            or ident=? 
        union
        all select
            bid,
            ident,
            location,
            sex,
            null as salary,
            null as species,
            null as hive,
            1 as clazz_ 
        from
            humans 
        where
            ident=? 
            or ident=? 
            or ident=? 
        union
        all select
            bid,
            ident,
            location,
            null as sex,
            null as salary,
            species,
            hive,
            3 as clazz_ 
        from
            aliens 
        where
            ident=? 
            or ident=? 
            or ident=? 
    ) being0_ 
where
    being0_.ident=? 
    or being0_.ident=? 
    or being0_.ident=?