Optimizer flag rowid_filter leads to slow query

 rowid_filter=on

MariaDB [marvin_production]> set optimizer_switch='rowid_filter=on';

MariaDB [marvin_production]> analyze SELECT `payment_sessions`.* FROM `payment_sessions` WHERE `payment_sessions`.`status` = 1 AND `payment_sessions`.`attempted` = FALSE AND `payment_sessions`.`payable_id` = 61700906 AND `payment_sessions`.`payable_type` = 'Ecommerce::Order' AND `payment_sessions`.`user_id` = 6204898 ORDER BY id desc;

+------+-------------+--------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+---------+-------------+--------+-----------+----------+------------+---------------------------------+

| id   | select_type | table                    | type       | possible_keys                                                                                                                                                                                                            | key                                                                                                     | key_len | ref         | rows   | r_rows    | filtered | r_filtered | Extra                           |

+------+-------------+--------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+---------+-------------+--------+-----------+----------+------------+---------------------------------+

|    1 | SIMPLE      | payment_sessions | ref|filter | index_payment_sessions_on_status_and_attempted,index_payment_sessions_on_payable_id_and_payable_type,index_payment_sessions_on_user_id,index_payment_sessions_on_status_attempted_and_updated_at | index_payment_sessions_on_payable_id_and_payable_type|index_payment_sessions_on_user_id | 773|5   | const,const | 1 (0%) | 1.00 (0%) |     0.00 |     100.00 | Using where; Using rowid filter |

+------+-------------+--------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------+---------+-------------+--------+-----------+----------+------------+---------------------------------+

1 row in set (6.237 sec)

MariaDB [marvin_production]> set optimizer_switch='rowid_filter=off';

Query OK, 0 rows affected (0.000 sec)

MariaDB [marvin_production]> analyze SELECT `payment_sessions`.* FROM `payment_sessions` WHERE `payment_sessions`.`status` = 1 AND `payment_sessions`.`attempted` = FALSE AND `payment_sessions`.`payable_id` = 61700906 AND `payment_sessions`.`payable_type` = 'Ecommerce::Order' AND `payment_sessions`.`user_id` = 6204898 ORDER BY id desc;

+------+-------------+--------------------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------+------+--------+----------+------------+-------------+

| id   | select_type | table                    | type | possible_keys                                                                                                                                                                                                            | key                                       | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra       |

+------+-------------+--------------------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------+------+--------+----------+------------+-------------+

|    1 | SIMPLE      | payment_sessions | ref  | index_payment_sessions_on_status_and_attempted,index_payment_sessions_on_payable_id_and_payable_type,index_payment_sessions_on_user_id,index_payment_sessions_on_status_attempted_and_updated_at | index_payment_sessions_on_user_id | 5       | const | 1    | 1.00   |     0.00 |     100.00 | Using where |

+------+-------------+--------------------------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------+---------+-------+------+--------+----------+------------+-------------+

1 row in set (0.000 sec)

{

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 20063.527,

    "table": {

      "table_name": "payment_sessions",

      "access_type": "ref",

      "possible_keys": [

        "index_payment_sessions_on_status_and_attempted",

        "index_payment_sessions_on_payable_id_and_payable_type",

        "index_payment_sessions_on_user_id",

        "index_payment_sessions_on_status_attempted_and_updated_at"

      ],

      "key": "index_payment_sessions_on_payable_id_and_payable_type",

      "key_length": "773",

      "used_key_parts": ["payable_id", "payable_type"],

      "ref": ["const", "const"],

      "rowid_filter": {

        "range": {

          "key": "index_payment_sessions_on_user_id",

          "used_key_parts": ["user_id"]

        },

        "rows": 1,

        "selectivity_pct": 3.321769e-6,

        "r_rows": 1,

        "r_lookups": 29100206,

        "r_selectivity_pct": 3.436402e-6,

        "r_buffer_size": 0,

        "r_filling_time_ms": 0.011

      },

      "r_loops": 1,

      "rows": 1,

      "r_rows": 1,

      "r_table_time_ms": 20063.509,

      "r_other_time_ms": 0.012,

      "r_engine_stats": {

        "pages_accessed": 82910,

        "pages_read_count": 65125,

        "pages_read_time_ms": 9629.967

      },

      "filtered": 3.88882e-7,

      "r_filtered": 100,

      "attached_condition": "payment_sessions.payable_id <=> 61700906 and payment_sessions.payable_type <=> 'Ecommerce::Order' and payment_sessions.`status` = 1 and payment_sessions.attempted = 0 and payment_sessions.user_id = 6204898 and payment_sessions.payable_type = 'Ecommerce::Order'"

    }

  }

}