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
- relates to
-
MDEV-26955 Improve the cost model for usage of in-memory range rowid / primary key filters
-
- Stalled
-
-
MDEV-29371 Rowid Filtering optimization can cause slowdowns
-
- Open
-
-
MDEV-34413 Index Condition Pushdown for reverse-ordered scans
-
- Closed
-
-
-
- Closed
-
Activity
if we want to Re-engineer the code and move handler->idx_cond_push() calls, we need a separate MDEV for this.
psergei The problem is that with the current design the first stage of the optimizer can be called several times producing several plans and calling make_join_readinfo() for each of them and consequently calling handler::idx_cond_push() for each of them. Who cancels unneeded ones is not clear.
Notes for the changelog:
Rowid Filter optimization cannot work with backward index scans. An attempt to run such query plan will make the query perform very slowly. Fixed by disabling use of Rowid Filter if the optimizer decides to use a backward index scan.
Sergei Petrunia The problem is that with the current design the first stage of the optimizer can be called several times producing several plans and calling make_join_readinfo() for each of them and consequently calling handler::idx_cond_push() for each of them. Who cancels unneeded ones is not clear.
igor this sounds contrary to anything I know about the code.
Can you please provide a testcase where make_join_readinfo(JOIN* j, ...) is called multiple times for the same JOIN object?
The only call to make_join_readinfo() is in JOIN::optimize_stage2()...
Pushed this cset with an added assert:
commit b47bd3f8bf5dba600c5b7dc46fb77c0a8a73508c (HEAD -> 10.5, origin/bb-10.5-MDEV-33875, origin/10.5, bb-10.5-MDEV-33875)Author: Sergei Petrunia <sergey@mariadb.com>Date: Fri Jun 14 12:46:56 2024 +0300MDEV-33875: ORDER BY DESC causes ROWID Filter slowdown