Crippling performance issues caused by hanging queries

After enabling Content Access module the performance of views (based on solr) worsened considerably to a point that they were timing out.

We found multiple and always growing number of mysqld processes to a point where the server practically stopped running.

This in turn was caused by multiple never completing queries (hundreds of seconds!) such as
SELECT COUNT(*) AS "expression" FROM (SELECT 1 AS "expression" FROM "node_field_data" "node_field_data" WHERE ("node_field_data"."type" IN ('strategy')) AND (EXISTS (SELECT "na"."nid" AS "nid" FROM "node_access" "na" WHERE ((("gid" IN ('0')) AND ("realm" = 'all')) OR (("gid" IN ('14')) AND ("realm" = 'content_access_author')) OR (("gid" IN ('2', '4')) AND ("realm" = 'content_access_roles'))) AND ("na"."grant_view" >= '1') AND (("node_field_data"."nid" = "na"."nid"))))) "subquery"

See attached screenshot as well:

Eventually had to switch off Content Access module, but we will gladly test it again if there is a solution to this problem.