Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: OR optimization seems strange or nonexistent #3887

Open
opomuc opened this issue Dec 17, 2018 · 0 comments
Open

sql: OR optimization seems strange or nonexistent #3887

opomuc opened this issue Dec 17, 2018 · 0 comments
Assignees
Labels

Comments

@opomuc
Copy link
Collaborator

opomuc commented Dec 17, 2018

box.sql.execute("create table t1(id int primary key, a int, b int, c int)")
box.sql.execute("create index i2 on t1(b)")
box.sql.execute("create index i3 on t1(c)")

for i=1,1000000 do
   box.space.T1:insert({i,10*i,i+1,i+100})
end


unix/:/var/run/tarantool/tarantool.sock> test = function(str)
                                       >    local start = clock.monotonic()
                                       >    a = box.sql.execute(str)
                                       >    return clock.monotonic() - start
                                       > end

test("select a,b,c from t1 where a > 10000 or b < 100 or c > 1000000")
---
- 0.29623069998343
...
box.sql.execute("explain query plan select a,b,c from t1 where a > 10000 or b < 100 or c > 1000000")
---
- - [0, 0, 0, 'SCAN TABLE T1']
...
box.sql.execute("create index i4 on t1(a)")
---
...

test("select a,b,c from t1 where a > 10000 or b < 100 or c > 1000000")
---
- 1.487278399989
...
box.sql.execute("explain query plan select a,b,c from t1 where a > 10000 or b < 100 or c > 1000000")
---
- - [0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX I4 (A>?)']
  - [0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX I2 (B<?)']
  - [0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX I3 (C>?)']
...

Execution with all covering indexes lasts ~3 times more than using fullscan

@opomuc opomuc added bug Something isn't working sql query optimizer labels Dec 17, 2018
@opomuc opomuc changed the title sql: OR optimization does not work sql: OR optimization seems strange or nonexistent Dec 17, 2018
@kyukhin kyukhin added this to the 2.2.0 milestone Dec 25, 2018
@kyukhin kyukhin modified the milestones: 2.2.0, 2.3.0 Mar 27, 2019
@kyukhin kyukhin modified the milestones: 2.3.1, 2.4.1 Oct 2, 2019
@kyukhin kyukhin modified the milestones: 2.4.1, 2.5.1 Jan 23, 2020
@kyukhin kyukhin modified the milestones: 2.5.1, 2.6.1 Apr 24, 2020
@kyukhin kyukhin modified the milestones: 2.6.1, wishlist Oct 23, 2020
@ImeevMA ImeevMA self-assigned this Jun 16, 2022
@kyukhin kyukhin added the teamL label Jun 17, 2022
@kyukhin kyukhin removed this from the wishlist milestone Jun 17, 2022
@igormunkin igormunkin removed the teamL label Oct 20, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants