Struggling with Laravel performance optimization on complex queries

hey folks,

i'm working on a pretty intense project for 'Laravel Quick Fix & Consultation' and running into a wall with performance on some of our client's more complex data operations. it's a reporting dashboard that needs to pull in tons of aggregated data from several tables.

the main pain point is specific Laravel performance optimization around our Eloquent queries. we've got a system where users can generate custom reports, involving multiple many-to-many relationships, several joins, and complex aggregations (sums, averages, counts across different timeframes). when the dataset grows, these queries just grind to a halt.

i've already tried the usual suspects:

• using select() to only fetch needed columns
• eager loading with with() for related models, but sometimes nested eager loading still hits n+1 issues on deeper relationships or when conditions are applied to the eager loaded models
• adding indexes to all relevant foreign keys and frequently queried columns
• basic query caching with remember() and Redis, but invalidating caches for real-time reporting is a nightmare, or the data gets stale too fast
• i've even gone as far as writing raw SQL for the most critical bottlenecks, but maintaining that alongside Eloquent is getting messy and hard to scale for dynamic reports.
• we've used Laravel Debugbar and Blackfire to pinpoint slow queries, and while it helps identify them, the solutions often lead back to the same problem: the sheer complexity of the data aggregation needed.

the specific technical block i'm facing is generating reports that require calculating metrics like "average time spent per user on X task, grouped by Y department, filtered by Z date range" where X, Y, and Z involve traversing several pivot tables and polymorphic relationships. even with highly optimized raw SQL, these queries can take 10-20 seconds on a medium-sized dataset, and we need near real-time.

i'm looking for advice beyond the standard Laravel performance optimization guides. have any of you dealt with extremely complex, real-time reporting requirements in Laravel? are there specific database tuning strategies (beyond just indexing) for MySQL or PostgreSQL that play especialy well with Laravel's ORM or even when dropping down to raw SQL? or perhaps architectural patterns like using a separate OLAP database, materialized views, or specific packages designed for heavy data analytics in a Laravel environment? any insights on advanced caching patterns for dynamic, aggregative data?

help a brother out please...