N+1 Query Performance Issue in Vets Endpoint-created-by-agentic

[shaykeren]

Issue Description

The Vets endpoint (/vets.html) is experiencing an N+1 query performance issue due to eager loading of specialties for each vet.

Root Cause

1. The Vet entity uses FetchType.EAGER for specialties, causing unnecessary queries
2. Missing proper join fetching strategy in the repository layer
3. No composite index on the vet_specialties table for optimized joins

Solution

A pull request (#81) has been created with the following changes:

1. Changed FetchType.EAGER to FetchType.LAZY in the Vet entity
2. Added @NamedEntityGraph for efficient fetching of Vet specialties
3. Updated VetRepository to use @EntityGraph annotation
4. Added composite index on vet_specialties(vet_id, specialty_id)

Impact

These changes will:

• Prevent unnecessary queries when specialties are not needed
• Optimize fetching when specialties are needed using join fetch
• Improve query performance with proper indexing

Testing

• The changes maintain existing functionality while improving performance
• The EntityGraph ensures efficient loading of specialties when needed
• The composite index will improve join performance

Related

• Original Issue: #e8efc2b4-5f5e-11f0-bbd9-0242ac160009
• Pull Request: Fix N+1 Query Issue in Vets Endpoint-created-by-agentic #81