Fix N+1 Query in Vets Endpoint-created-by-agentic

[shaykeren]

Issue Description

The /vets.html endpoint is experiencing an N+1 query performance issue when loading veterinarians and their specialties. This is causing unnecessary database queries and impacting performance.

Root Cause

The current implementation uses FetchType.EAGER for the specialties relationship in the Vet entity, which leads to inefficient loading of related data.

Proposed Changes

1. Change FetchType.EAGER to FetchType.LAZY in the Vet entity
2. Add @NamedEntityGraph to optimize loading of specialties
3. Update VetRepository to use the entity graph for efficient loading
4. Add necessary database indexes on vet_specialties.vet_id and specialties.id

Implementation Details

@Entity
@Table(name = "vets")
@NamedEntityGraph(name = "Vet.specialties",
    attributeNodes = @NamedAttributeNode("specialties")
)
public class Vet extends Person {
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "vet_specialties",
        joinColumns = @JoinColumn(name = "vet_id"),
        inverseJoinColumns = @JoinColumn(name = "specialty_id"))
    private Set<Specialty> specialties;
    // ... rest of the class
}

public interface VetRepository extends Repository<Vet, Integer> {
    @EntityGraph(value = "Vet.specialties")
    @Transactional(readOnly = true)
    @Cacheable("vets")
    Collection<Vet> findAll() throws DataAccessException;
    
    @EntityGraph(value = "Vet.specialties")
    @Transactional(readOnly = true)
    @Cacheable("vets")
    Page<Vet> findAll(Pageable pageable) throws DataAccessException;
}

Database Changes

Add the following indexes if not already present:

CREATE INDEX IF NOT EXISTS idx_vet_specialties_vet_id ON vet_specialties(vet_id);
CREATE INDEX IF NOT EXISTS idx_specialties_id ON specialties(id);

Expected Outcome

• Reduced number of database queries when loading vets and their specialties
• Improved response time for the /vets.html endpoint
• Better overall application performance