How to efficiently search inherited entities with JOINED strategy in JPA (Person or Organization as Partner)

I'm working on a Spring Boot app using JPA and I'm facing a performance concern with a search query over an inheritance hierarchy.

📘 What I'm building

I have a Partner entity which is the parent class of two subclasses:

• Person: has firstName, lastName
• Organization: has companyName, registrationNumber, taxNumber

I'm using the JOINED inheritance strategy in JPA, so each entity has its own table. The Partner class also contains shared fields like:

• id, email, partnerType (enum: CLIENT or SUPPLIER), entityType (enum: PERSON or ORGANIZATION), etc.

This setup allows a partner (client or supplier) to be either a person or an organization.

✅ What I want to do

I need to build a paginated search that fetches all partners of a certain partnerType, and optionally filters by a searchTerm:

• If it's a Person, search firstName + lastName
• If it's an Organization, search companyName

The result is mapped into a DTO that combines data from Partner, Person, and Organization.

💬 What I’ve done

Here’s the JPQL query I’m using:

@Query("""
SELECT new com.example.api.PartnerResponseDto(
    p.id,
    p.partnerType,
    p.entityType,
    p.email,
    p.createdAt,
    p.updatedAt,
    pe.firstName,
    pe.lastName,
    o.companyName,
    o.registrationNumber,
    o.taxNumber
)
FROM Partner p
LEFT JOIN Person pe ON p.id = pe.id
LEFT JOIN Organization o ON p.id = o.id
WHERE p.partnerType = :partnerType
AND (
    :searchTerm IS NULL OR :searchTerm = ''
    OR (
        (p.entityType = 'PERSON' AND CONCAT(pe.firstName, ' ', pe.lastName) LIKE CONCAT('%', :searchTerm, '%'))
        OR (p.entityType = 'ORGANIZATION' AND o.companyName LIKE CONCAT('%', :searchTerm, '%'))
    )
)
""")
Page<PartnerResponseDto> findPartners(
    @Param("partnerType") PartnerType partnerType,
    @Param("searchTerm") String searchTerm,
    Pageable pageable
);

It works fine and returns the expected results. But I'm starting to worry about the performance as the data grows.

⚠️ The Problem

This query includes:

• Two LEFT JOINs (to both Person and Organization)
• An OR condition in the WHERE clause
• CONCAT and LIKE usage for searching

This makes the query a bit heavy and not so clean — especially considering it always joins both tables even when the entityType is only one of them.

I’d love to hear any suggestions or feedback, especially from folks who’ve dealt with similar inheritance + filtering cases in JPA. Thanks in advance!

Here’s what I’d do next to make this faster and more scalable:

Convert it to a native SQL query – It gives you way more control and helps avoid JPA-generated queries that look good on paper but perform poorly in practice.

Make sure you check your indexes for the key joining and querying columns:

• Index on partner_type and entity_type in the partner table

• Functional index on CONCAT(first_name, ' ', last_name) for people

• Regular index on company_name for organizations

When you rewrite it in native SQL, use your DBMS tools (like EXPLAIN ANALYZE) to show the execution plan.

Look out for:

1. Try to avoid hash joins if possible

2. Be careful with OR + wildcard LIKEs (LIKE '%search%')

3. Consider using UNION ALL instead of OR when searching across types