Magento 2.3 crashing on never-ending search_tmp_* queries

Question

I'm using Magento 2.3.2 and a production site that has a decently large number of products. The site has been running fine for over a year but in the last week we suddenly have an issue where a runaway query will take up all of the site CPU and, over time, crash the site.

I can show processlist in MySQL and the query looks like this:

INSERT INTO `search_tmp_5f9b11f4bfb569_80230545` SELECT `main_select`.`entity_id`, SUM(score) AS `re`

This query will never finish, and then another one appears, and then more, and eventually the site crashes. I can restart the database, or I can kill the job as root, but they keep coming back.

This question has come up before on Stack Exchange, but I've tried the various suggestions to no luck, and also ours was not started by a specific action or change. We do not have Amasty Sorting Module installed.

One thing I did try was the patch described at this Github thread. This was a local edit: changing entity_id to source_id in the file CustomAttributeFilter. That did nothing, in fact it didn't even change the query that I can see, which surprised me.

I am also having issues getting Elastic Search to work properly with our current setup, which I think is unrelated but which has also been a suggested fix.

At this point I just want my site to function and not crash without regular manual intervention. Can someone advise where I should be looking, or what kind of further research I should do? I'd like to know for sure what pages are even causing this in the first place, and how to duplicate the issue on a different server so I can troubleshoot outside of production.

Edit 1: I have gotten around this issue by setting up a cron job running once a minute that kills every query starting with "INSERT INTO `search_tmp_". My site appears to be unaffected and does not run out of memory and crash. Of course this is not a fix. I'd still like to know the source of this error at the very least.

Edit 2: Full query using full processlist (of course the hex code after search_tmp_ is different each iteration)

INSERT INTO `search_tmp_5fc5942c05f071_72644368` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0) + (0) + (0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
  INNER JOIN `catalog_product_index_eav` AS `brand_id_filter` ON `search_index`.`source_id` = `brand_id_filter`.`source_id` AND `brand_id_filter`.`attribute_id` = 161 AND `brand_id_filter`.`store_id` = 1
  INNER JOIN `catalog_product_index_eav` AS `group_filter` ON `search_index`.`source_id` = `group_filter`.`source_id` AND `group_filter`.`attribute_id` = 192 AND `group_filter`.`store_id` = 1
  INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_status` = 1 AND `stock_index`.`stock_id` = 1
  INNER JOIN `cataloginventory_stock_status` AS `sub_products_stock_index` ON sub_products_stock_index.product_id = search_index.source_id AND `sub_products_stock_index`.`website_id` = 0 AND `sub_products_stock_index`.`stock_status` = 1 AND `sub_products_stock_index`.`stock_id` = 1
  INNER JOIN `catalog_product_index_eav` AS `visibility_filter` ON search_index.entity_id = visibility_filter.entity_id AND `visibility_filter`.`attribute_id` = 99 AND `visibility_filter`.`value` in ('2', '4') AND `visibility_filter`.`store_id` = '1'
  INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1'
  INNER JOIN `catalog_product_index_price` AS `price_index` ON search_index.entity_id = price_index.entity_id AND price_index.website_id = '1' WHERE (search_index.store_id = '1') AND (category_ids_index.category_id = 151) AND (`price_index`.`min_price` >= '3000' AND `price_index`.`min_price` <= '4000' AND `price_index`.`customer_group_id` = 0) AND (brand_id_filter.value  IN ('1')) AND (group_filter.value = '514')) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
  LIMIT 10000

Edit 3:

The select query, run by itself, takes no time at all to run alone and give results. It seems like the culprit table with index issues is the tmp table itself, which is of course transient.

Thank you folks for your interest in helping. These details were requested in comments, and are pasted in here without comment because I don't quite know what I should be looking for.

  CREATE TABLE `catalog_product_index_eav` (
    `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
    `attribute_id` smallint(5) unsigned NOT NULL COMMENT 'Attribute ID',
    `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
    `value` int(10) unsigned NOT NULL COMMENT 'Value',
    `source_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Original entity Id for attribute value',
    PRIMARY KEY (`entity_id`,`attribute_id`,`store_id`,`value`,`source_id`),
    KEY `CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID` (`attribute_id`),
    KEY `CATALOG_PRODUCT_INDEX_EAV_STORE_ID` (`store_id`),
    KEY `CATALOG_PRODUCT_INDEX_EAV_VALUE` (`value`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product EAV Index Table';

  CREATE TABLE `cataloginventory_stock_status` (
    `product_id` int(10) unsigned NOT NULL COMMENT 'Product Id',
    `website_id` smallint(5) unsigned NOT NULL COMMENT 'Website Id',
    `stock_id` smallint(5) unsigned NOT NULL COMMENT 'Stock Id',
    `qty` decimal(12,4) NOT NULL DEFAULT '0.0000' COMMENT 'Qty',
    `stock_status` smallint(5) unsigned NOT NULL COMMENT 'Stock Status',
    PRIMARY KEY (`product_id`,`website_id`,`stock_id`),
    KEY `CATALOGINVENTORY_STOCK_STATUS_STOCK_ID` (`stock_id`),
    KEY `CATALOGINVENTORY_STOCK_STATUS_WEBSITE_ID` (`website_id`),
    KEY `CATALOGINVENTORY_STOCK_STATUS_STOCK_STATUS` (`stock_status`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Cataloginventory Stock Status';

  CREATE TABLE `catalog_category_product_index_store1` (
    `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Category Id',
    `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product Id',
    `position` int(11) DEFAULT NULL COMMENT 'Position',
    `is_parent` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Is Parent',
    `store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store Id',
    `visibility` smallint(5) unsigned NOT NULL COMMENT 'Visibility',
    PRIMARY KEY (`category_id`,`product_id`,`store_id`),
    KEY `CAT_CTGR_PRD_IDX_STORE1_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY` (`product_id`,`store_id`,`category_id`,`visibility`),
    KEY `IDX_216E521C8AD125E066D2B0BAB4A08412` (`store_id`,`category_id`,`visibility`,`is_parent`,`position`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Category Product Index Store1';

  CREATE TABLE `catalog_product_index_price` (
    `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
    `customer_group_id` int(10) unsigned NOT NULL COMMENT 'Customer Group ID',
    `website_id` smallint(5) unsigned NOT NULL COMMENT 'Website ID',
    `tax_class_id` smallint(5) unsigned DEFAULT '0' COMMENT 'Tax Class ID',
    `price` decimal(12,4) DEFAULT NULL COMMENT 'Price',
    `final_price` decimal(12,4) DEFAULT NULL COMMENT 'Final Price',
    `min_price` decimal(12,4) DEFAULT NULL COMMENT 'Min Price',
    `max_price` decimal(12,4) DEFAULT NULL COMMENT 'Max Price',
    `tier_price` decimal(12,4) DEFAULT NULL COMMENT 'Tier Price',
    PRIMARY KEY (`entity_id`,`customer_group_id`,`website_id`),
    KEY `CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID` (`customer_group_id`),
    KEY `CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE` (`min_price`),
    KEY `CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE` (`website_id`,`customer_group_id`,`min_price`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog Product Price Index Table';