Struggling with Laravel Eloquent query optimization performance

Context: i'm developing 'Laravel Quick Fix & Consultation', and we're seeing some pretty gnarly performance hits on data retrieval. everything was fine on smaller datasets, but now with more users and data, certain pages are just crawling.

Problem: the main culprit seems to be complex Eloquent queries, especially when dealing with nested relationships and aggregations for reporting. i've tried eager loading (with(), load()) extensively, but i'm still seeing an unacceptable number of queries or the queries themselves are just too slow. it feels like i'm missing a fundamental aspect of advanced eloquent query optimization.

here's a simplified version of a problematic query that's causing grief:

// Example of a slow query
Project::whereHas('tasks', function ($query) {
    $query->where('status', 'pending');
})
->with(['tasks' => function ($query) {
    $query->select('id', 'project_id', 'name', 'status', 'due_date');
}, 'client'])
->withCount(['tasks' => function ($query) {
    $query->where('status', 'completed');
}])
->get();

this particular query, when run against a decent number of projects and tasks, often results in several dozen individual queries or a single query that takes seconds.

Specific Question: what are the most effective strategies for profiling and then refactoring these kinds of complex Eloquent queries? are there any less obvious Laravel techniques or database indexing approaches i should be looking into to significantly improve performance beyond basic eager loading? anyone faced this before?