sql uses full scan when supposed primary key usage
Environment:
OS: Ubuntu 22.04.2 LTS
tarantool: 2.10.5-14-ga646920
Expected Behavior:
primary index is used for selection if where
clause with in
operator use all the primary index parts
Actual Behavior:
there are cases when full scan used instead of primary index
Steps to Reproduce:
- Create space with index of two fields:
box.schema.space.create('SCAN_SPACE', {
format = {
{name = 'ID', type = 'integer'},
{name = 'ID2', type = 'integer'},
},
if_not_exists = true,
})
box.space.SCAN_SPACE:create_index('ID', {
type = 'TREE',
unique = true,
parts = {{field = 'ID', type ='integer'} , {field = 'ID2', type ='integer'}},
name = 'ID',
})
- Load the data
for i=1,500000 do
box.space.SCAN_SPACE:insert{i, i+1}
end
- Execute
EXPLAIN QUERY PLAN
statement:
box.execute([[EXPLAIN QUERY PLAN SELECT id, id2 from SCAN_SPACE where (id, id2) in (values (1, 2), (2, 3))]])
---
- metadata:
- name: selectid
type: integer
- name: order
type: integer
- name: from
type: integer
- name: detail
type: text
rows:
- [0, 0, 0, 'SCAN TABLE SCAN_SPACE (~983040 rows)']
- [0, 0, 0, 'EXECUTE LIST SUBQUERY 1']
As you can see, we want to use where clause
for pair (id, id2), that are parts of primary index, also its order is corresponding to index. However, the index is not used, you can see full scan here.
If only one values
is used, primary key is present in the plan.
box.execute([[EXPLAIN QUERY PLAN SELECT id, id2 from SCAN_SPACE where (id, id2) in (values (1, 2))]])
---
- metadata:
- name: selectid
type: integer
- name: order
type: integer
- name: from
type: integer
- name: detail
type: text
rows:
- [0, 0, 0, 'SEARCH TABLE SCAN_SPACE USING PRIMARY KEY (ID=? AND ID2=?) (~24 rows)']
- [0, 0, 0, 'EXECUTE LIST SUBQUERY 1']
Also primary index is used for selection with where clause
by one field with in
operator.
box.execute([[EXPLAIN QUERY PLAN SELECT id from SCAN_SPACE where id in (1, 2, 3)]])
---
- metadata:
- name: selectid
type: integer
- name: order
type: integer
- name: from
type: integer
- name: detail
type: text
rows:
- [0, 0, 0, 'SEARCH TABLE SCAN_SPACE USING PRIMARY KEY (ID=?) (~30 rows)']
- [0, 0, 0, 'EXECUTE LIST SUBQUERY 1']