Stuck on Laravel troubleshooting

Hey everyone,

I'm hitting a wall with a recent Laravel 10 upgrade and hoping some of you seasoned devs might have insights. We recently upgraded one of our core SaaS applications from Laravel 9 to Laravel 10, which was supposed to be a smooth transition, but we've run into a major snag.

The main issue is a significant performance drop in a critical reporting module. This module generates complex analytics reports for our users. Specifically, a particular Eloquent query, which involves multiple joins, subqueries, and aggregations across several large tables, now takes an agonizing 10-20 seconds to execute. It often times out on larger datasets, whereas it was consistently fast (under 2 seconds) on Laravel 9.

Here's what I've tried so far:

• Verified all database indexes are still intact and optimized. Ran EXPLAIN on the generated SQL and indices seem to be utilized.
• Used DB::listen() to analyze the actual SQL queries being generated by Eloquent. They look correct, no obvious N+1 issues or excessive queries.
• Experimented heavily with eager loading (with()) and load() for related models, but this specific query is more about complex joins on the main dataset rather than just fetching relationships.
• Attempted to refactor parts of the query into raw SQL using DB::raw() and even creating a view for some aggregations. This gave minor gains (maybe shaving off a second or two), but it's still unacceptably slow.
• Checked server resource utilization (CPU, RAM, I/O) during the query execution. While CPU spikes, there are no obvious bottlenecks indicating server capacity issues.
• Thoroughly reviewed the Laravel 10 upgrade guide for any breaking changes related to Eloquent, query builder, or database interactions that might explain this regression. Nothing jumped out directly related to this kind of performance hit.

Here's a simplified version of the problematic query (it's much larger in reality, but this captures the essence of the complexity and aggregations):

// Simplified problematic query example
$reportData = Report::query()
    ->select([
        'reports.id',
        'reports.name',
        DB::raw('COUNT(DISTINCT transactions.id) as total_transactions'),
        DB::raw('SUM(transactions.amount) as total_revenue'),
        DB::raw('AVG(transaction_items.quantity) as avg_item_quantity')
    ])
    ->join('transactions', 'reports.id', '=', 'transactions.report_id')
    ->leftJoin('transaction_items', 'transactions.id', '=', 'transaction_items.transaction_id')
    ->where('reports.status', 'completed')
    ->whereDate('reports.created_at', '>=', $startDate)
    ->whereDate('reports.created_at', '<=', $endDate)
    ->groupBy('reports.id', 'reports.name')
    ->orderBy('total_revenue', 'desc')
    ->get();

// Example of a typical timeout error we're seeing
/*
[2023-10-27 10:35:12] production.ERROR: Maximum execution time of 30 seconds exceeded (SQL: SELECT ...) {"exception":"[object] (Symfony\Component\ErrorHandler\Error\FatalError(code: 0): Maximum execution time of 30 seconds exceeded at /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php:712)
*/

I'm really scratching my head here. I'm looking for advice on advanced Laravel performance optimization strategies, especially for debugging complex Eloquent queries post-upgrade. Are there any common pitfalls in Laravel 10 or specific configuration changes that might lead to such significant slowdowns that I might have missed? Or perhaps tools/methods for deeper database query analysis within a Laravel context?

Anyone faced this before?