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
A testcase demonstrating the issue:
create table t1 ( |
pk int primary key auto_increment, |
a int, |
b int, |
f1 varchar(200), |
f2 varchar(200), |
f3 varchar(200), |
f4 varchar(200), |
f5 varchar(200), |
key(a, pk), |
key(b) |
);
|
|
|
insert into t1 (a,b,f1, f2, f3, f4) select |
seq, seq,
|
repeat('1-', 100), |
repeat('2-', 100), |
repeat('3-', 100), |
repeat('4-', 100) |
from
|
seq_1_to_20000;
|
|
|
insert into t1 (a,b,f1, f2, f3, f4)select |
30100, 30100,
|
uuid(), uuid(), uuid(), uuid()
|
from
|
seq_1_to_1000;
|
|
|
insert into t1 (a,b,f1) values ( 110, 100, 12345); |
analyze table t1; |
|
|
analyze format=json select * from t1 |
where
|
a =30100 and b in (30100,30101,30102) |
order by |
pk desc; |
produces
{
|
"query_block": { |
"select_id": 1, |
"r_loops": 1, |
"r_total_time_ms": 75.56, |
"table": { |
"table_name": "t1", |
"access_type": "ref", |
"possible_keys": ["a", "b"], |
"key": "a", |
"key_length": "5", |
"used_key_parts": ["a"], |
"ref": ["const"], |
"rowid_filter": { |
"range": { |
"key": "b", |
"used_key_parts": ["b"] |
},
|
"rows": 1002, |
"selectivity_pct": 5.24387691, |
"r_rows": 1000, |
"r_lookups": 21001, |
"r_selectivity_pct": 4.761678015, |
"r_buffer_size": 500, |
"r_filling_time_ms": 2.085 |
},
|
"r_loops": 1, |
"rows": 1000, |
"r_rows": 1000, |
"r_table_time_ms": 74.339, |
"r_other_time_ms": 0.907, |
"r_engine_stats": { |
"pages_accessed": 3028 |
},
|
"filtered": 5.243876934, |
"r_filtered": 100, |
"attached_condition": "t1.a <=> 30100 and t1.b in (30100,30101,30102)" |
}
|
}
|
}
|
note the
"rowid_filter": {
|
...
|
"r_lookups": 21001,
|
Note that range access is affected, too:
analyze format=json select * from t1 where a in (30100,60000) and b in (30100,30101,30102) order by a desc, pk desc; |
shows range+Rowid Filter, without filesort.
This is how a similar issue with Index Condition Pushdown was resolved by disabling ICP:
commit 1d3ba8a791c50b7fe7036470d5f26e3f6525156d
|
Author: Sergey Petrunya <psergey@askmonty.org> Wed May 23 11:46:40 2012
|
Committer: Sergey Petrunya <psergey@askmonty.org> Wed May 23 11:46:40 2012
|
|
|
BUG#1000051: Query with simple join and ORDER BY takes thousands times longer when run with ICP
|
- Disable IndexConditionPushdown for reverse scans.
|
bb-10.5-MDEV-33875
commit fdf86683479959a569d9a71da60b209e68ad1f09 (HEAD -> bb-10.5-MDEV-33875, origin/bb-10.5-MDEV-33875)
|
Author: Sergei Petrunia <sergey@mariadb.com>
|
Date: Fri Jun 14 12:46:56 2024 +0300
|
|
|
MDEV-33875: ORDER BY DESC causes ROWID Filter slowdown
|
|
Rowid Filter cannot be used with reverse-ordered scans, for the
|
same reason as IndexConditionPushdown cannot be.
|
|
test_if_skip_sort_order() already has logic to disable ICP when
|
setting up a reverse-ordered scan. Added logic to also disable
|
Rowid Filter in this case, factored out the code into
|
prepare_for_reverse_ordered_access(), and added a comment describing
|
the cause of this limitation.
|
ok to push.
Please create a Jira entry to add the MySQL optimization to inform engine of the end key range
psergei, monty re this code of the approved commit:
if (tab->select && tab->select->pre_idx_push_select_cond)
|
{
|
tab->set_cond(tab->select->pre_idx_push_select_cond);
|
tab->table->file->cancel_pushed_idx_cond();
|
}
|
/*
|
The same with Rowid Filter: it doesn't work with reverse scans so cancel
|
it, too.
|
*/
|
{
|
tab->range_rowid_filter_info= NULL;
|
delete tab->rowid_filter;
|
tab->rowid_filter= NULL;
|
}
|
Why
tab->table->file->cancel_pushed_idx_cond();
is called and
tab->table->file->cancel_pushed_rowid_filter();
is not called?
igor,
join optimization proceeds as follows:
1. make_join_readinfo() calls push_index_cond() which calls handler::idx_cond_push().
2. JOIN::optimize_stage2() calls test_if_skip_sort_order() which may call prepare_for_reverse_ordered_access() (the code in question).
3. JOIN::optimize_stage2() calls JOIN::init_range_rowid_filters() which calls handler::rowid_filter_push().
Note that step #3 is done after step #2.
So, in the current code, at step #2 one needs to call cancel_pushed_idx_cond but not cancel_pushed_rowid_filter().
I think I'll add an assert in prepare_for_reverse_ordered_access() that there's no pushed rowid filter. Or do you think it is still a good idea to call cancel_pushed_rowid_filter() anyway?
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 +0300
|
|
|
MDEV-33875: ORDER BY DESC causes ROWID Filter slowdown
|
|
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()...
A related patch:
commit 8d85715d507de8937a181e999501e205ff3dca34Author: Sergei Petrunia <psergey@askmonty.org>Date: Wed May 6 15:37:19 2020 +0300MDEV-21794: Optimizer flag rowid_filter leads to long queryRowid Filter check is just like Index Condition Pushdown check: beforewe check the filter, we must check if we have walked out of the rangewe are scanning. (If we did, we should return, and not continue the scan).Consequences of this:- Rowid filtering doesn't work for keys that have partially-coveredblob columns (just like Index Condition Pushdown)- The rowid filter function has three return values: CHECK_POS (passed)CHECK_NEG (filtered out), CHECK_OUT_OF_RANGE.All of the above is implemented in this patch