Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33875

ORDER BY DESC causes ROWID Filter optimization performance degradation

Details

    Description

      First off: I am not a DB expert.
      Queries below are performed in a large table with around 130.000.000 entries.
      I am using Mariadb 10.6.16

      There are multiple issues reported on ROWID filter performance degradation, I am not sure if this matches an existing/open issue or not.

      From time to time a SELECT query takes minutes where I would expect it to take milliseconds, I noticed that in every occurrence, the amount of entries returned (or matching the query) was very small.

      The problematic query is as follows: (Hibernate generated, reduced to make it readable)

      SELECT * 
       FROM 
      `payment_transaction` pt1_0 where pt1_0.`merchant_id`='<some id>' AND 
      pt1_0.`device_id`='<some id>' 
      order by pt1_0.`timestamp` desc 
      

      There are both indices starting with MERCHANT_ID and DEVICE_ID, using either of these indices would limit the result to a mere 55 entries in 1 case. However, the query takes 3-4 minutes to complete.

      I ran the following :

      SET SESSION optimizer_switch='rowid_filter=on';
       
      DESCRIBE FORMAT=JSON
      SELECT SQL_NO_CACHE * 
       FROM 
      `payment_transaction` pt1_0 where pt1_0.`merchant_id`='<some id>' and 1=1 AND 
      pt1_0.`device_id`='<some other id>' 
      order by pt1_0.`timestamp` desc 
      

      This yields:

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "pt1_0",
            "access_type": "ref",
            "possible_keys": [
              "idx_merchant_transactions",
              "idx_device_id_shoplocation_id_payment_method_timestamp",
              "idx_merchant_location",
              "idx_device_seq_number"
            ],
            "key": "idx_merchant_transactions",
            "key_length": "109",
            "used_key_parts": ["merchant_id"],
            "ref": ["const"],
            "rowid_filter": {
              "range": {
                "key": "idx_device_seq_number",
                "used_key_parts": ["device_id"]
              },
              "rows": 55,
              "selectivity_pct": 4.785047e-5
            },
            "rows": 55,
            "filtered": 4.785047e-5,
            "attached_condition": "pt1_0.merchant_id <=> '<some id>' and pt1_0.merchant_id = '<some id>' and pt1_0.device_id = '<some other id>'"
          }
        }
      }
      

      The total amount of rows is thus 55 (out of 132.000.000), which are returned instantly when using any of the four possible indices, but takes minutes with ROWID filtering.

      The same query without ROWID filter:

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "pt1_0",
            "access_type": "ref",
            "possible_keys": [
              "idx_merchant_transactions",
              "idx_device_id_shoplocation_id_payment_method_timestamp",
              "idx_merchant_location",
              "idx_device_seq_number"
            ],
            "key": "idx_merchant_transactions",
            "key_length": "109",
            "used_key_parts": ["merchant_id"],
            "ref": ["const"],
            "rows": 55,
            "filtered": 4.783742e-5,
            "attached_condition": "pt1_0.merchant_id <=> '<some id>' and pt1_0.merchant_id = '<some id>' and pt1_0.device_id = '<some other id>'"
          }
        }
      }
      

      Attachments

        Issue Links

          Activity

            bvanseg Bart Vansegbroeck created issue -
            alice Alice Sherepa made changes -
            Field Original Value New Value
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            Assignee Sergei Petrunia [ psergey ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.6 [ 24028 ]
            valerii Valerii Kravchuk made changes -
            Assignee Sergei Petrunia [ psergey ] Sergei Golubchik [ serg ]
            julien.fritsch Julien Fritsch made changes -
            Assignee Sergei Golubchik [ serg ] Sergei Petrunia [ psergey ]
            julien.fritsch Julien Fritsch made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            psergei Sergei Petrunia made changes -
            Summary ROWID Filter optimization performance degradation ORDER BY DESC causes ROWID Filter optimization performance degradation
            psergei Sergei Petrunia made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ] Michael Widenius [ monty ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            monty Michael Widenius made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            monty Michael Widenius made changes -
            Assignee Michael Widenius [ monty ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            Labels rowid_filtering
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.5.26 [ 29832 ]
            Fix Version/s 10.6.19 [ 29833 ]
            Fix Version/s 10.11.9 [ 29834 ]
            Fix Version/s 11.1.6 [ 29835 ]
            Fix Version/s 11.2.5 [ 29836 ]
            Fix Version/s 11.4.3 [ 29837 ]
            Fix Version/s 11.5.2 [ 29838 ]
            Fix Version/s 11.3.2 [ 29522 ]
            Fix Version/s 10.6 [ 24028 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            psergei Sergei Petrunia made changes -
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201682
            Zendesk active tickets 201682
            Gosselin Dave Gosselin made changes -

            People

              psergei Sergei Petrunia
              bvanseg Bart Vansegbroeck
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.