Extremely slow usergroup filtering (30+ seconds per page load) #1294
Description
Hi all,
We are experiencing a major performance problem in Icinga Web 2 when loading monitoring views.
Every page load that requires authorization checks on usergroup takes 30 seconds or more, making the UI almost unusable for organizations with many users and user groups.
After checking logs and profiling the database, we identified that the performance bottleneck is caused by an extremely complex SQL statement generated for permission checks. The core of the slow query is the following
PDOStatement::execute(select ... from usergroup where (((((usergroup.id not in ((select ? from host sub_host left join notification sub_host_notification on sub_host_notification.host_id = sub_host.id join notification_recipient sub_host_notification_notification_recipient on sub_host_notification_notification_recipient.notification_id = sub_host_notification.id join usergroup sub_host_notification_usergroup on sub_host_notification_usergroup.id = sub_host_notification_notification_recipient.usergroup_id where (sub_host.id is not null) and (sub_host_notification_usergroup.id is not null) group by sub_host_notification_usergroup.id having count(distinct ?) >= ?)) or usergroup.id is null)) and ((usergroup.id not in ((select ? from service sub_service left join notification sub_service_notification on sub_service_notification.service_id = sub_service.id join notification_recipient sub_service_notification_notification_recipient on sub_service_notification_notification_recipient.notification_id = sub_service_notification.id join usergroup sub_service_notification_usergroup on sub_service_notification_usergroup.id = sub_service_notification_notification_recipient.usergroup_id where (sub_service.id is not null) and (sub_service_notification_usergroup.id is not null) group by sub_service_notification_usergroup.id having count(distinct ?) >= ?)) or usergroup.id is null))) or (usergroup.id in ((select ? from customvar_flat sub_customvar_flat join host_customvar sub_customvar_flat_t_host_customvar on sub_customvar_flat_t_host_customvar.customvar_id = sub_customvar_flat.customvar_id join host sub_customvar_flat_host on sub_customvar_flat_host.id = sub_customvar_flat_t_host_customvar.host_id left join notification sub_customvar_flat_host_notification on sub_customvar_flat_host_notification.host_id = sub_customvar_flat_host.id join notification_recipient sub_customvar_flat_host_notification_notification_recipient on sub_customvar_flat_host_notification_notification_recipient.notification_id = sub_customvar_flat_host_notification.id join usergroup sub_customvar_flat_host_notification_usergroup on sub_customvar_flat_host_notification_usergroup.id = sub_customvar_flat_host_notification_notification_recipient.usergroup_id where (sub_customvar_flat.flatname = ?) and (sub_customvar_flat.flatvalue = ?))))) or ((((usergroup.id not in ((select ? from host sub_host left join notification sub_host_notification on sub_host_notification.host_id = sub_host.id join notification_recipient sub_host_notification_notification_recipient on sub_host_notification_notification_recipient.notification_id = sub_host_notification.id join usergroup sub_host_notification_usergroup on sub_host_notification_usergroup.id = sub_host_notification_notification_recipient.usergroup_id where (sub_host.id is not null) and (sub_host_notification_usergroup.id is not null) group by sub_host_notification_usergroup.id having count(distinct ?) >= ?)) or usergroup.id is null)) and ((usergroup.id not in ((select ? from service sub_service left join notification sub_service_notification on sub_service_notification.service_id = sub_service.id join notification_recipient sub_service_notification_notification_recipient on sub_service_notification_notification_recipient.notification_id = sub_service_notification.id join usergroup sub_service_notification_usergroup on sub_service_notification_usergroup.id = sub_service_notification_notification_recipient.usergroup_id where (sub_service.id is not null) and (sub_service_notification_usergroup.id is not null) group by sub_service_notification_usergroup.id having count(distinct ?) >= ?)) or usergroup.id is null))) or (usergroup.id in ((select ? from customvar_flat sub_customvar_flat join host_customvar sub_customvar_flat_t_host_customvar on sub_customvar_flat_t_host_customvar.customvar_id = sub_customvar_flat.customvar_id join host sub_customvar_flat_host on sub_customvar_flat_host.id = sub_customvar_flat_t_host_customvar.host_id left join notification sub_customvar_flat_host_notification on sub_customvar_flat_host_notification.host_id = sub_customvar_flat_host)
Could you check if this query can be optimized?