struggling with Laravel performance optimization on heavy Eloquent queries

hey everyone, i'm running a 'Laravel Quick Fix & Consultation' service, and we often deal with some pretty gnarly client issues. right now, i'm knee-deep in optimizing a project that involves super intense data processing for custom reporting. the client has some complex reporting endpoints, and under even moderate concurrent user loads, we're seeing severe performance degradation.

the primary suspect is definitely the complex Eloquent queries. we're talking multiple deeply nested polymorphic relationships, heavy GROUP BY clauses, and a bunch of aggregates. i've already gone through the usual suspects: extensive eager loading, adding all the relevant database indexes, experimenting with ->chunk() and ->cursor() for large data sets, and even caching frequently accessed aggregate data with Redis. but still, php artisan debugbar keeps pointing to specific query sets taking 500ms+ consistently. it's not just N+1; it feels like the sheer cost of relationship hydration and the ORM's overhead on these deeply nested polymorphic structures within complex joins is the bottleneck.

here's a snippet showing a typical debugbar output for one of the problematic queries:

[Query Log]
SELECT
  COUNT(DISTINCT `t1`.`id`) AS `aggregate`,
  `t2`.`name` AS `category_name`
FROM `reports` AS `t1`
INNER JOIN `report_categories` AS `t2`
  ON `t1`.`category_id` = `t2`.`id`
WHERE `t1`.`reportable_type` = 'App\\Models\\UserReport'
  AND `t1`.`created_at` BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59'
GROUP BY `t2`.`name`
ORDER BY `aggregate` DESC
LIMIT 10
-- Duration: 685.23ms, Rows: 150000+

i'm really trying to avoid just dropping into raw SQL for *every* single complex report, as it defeats the purpose of using Eloquent and makes maintenance a nightmare. how do you guys effectively tackle Laravel performance optimization for these types of deeply nested, polymorphic, aggregate queries? are there specific Eloquent patterns or maybe some lesser-known third-party packages that are good at handling this gracefully? anyone faced this before?