[jira] [Comment Edited] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.

["Taras Ledkov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16623913#comment-16623913 ] 

Taras Ledkov edited comment on IGNITE-4150 at 9/21/18 5:25 PM:
---------------------------------------------------------------

[~vozerov]
Simple join benchmark configuration:
- standalone Ignite node, queries run locally;
- tables:
{{CREATE TABLE person (id long, orgId long, name varchar, PRIMARY KEY (id, orgId))}}
{{CREATE TABLE organization (id long primary key, name varchar)}}
- Person contains 100K rows, Organization - 10K rows;
- queries:
selects 1 row: {{SELECT * FROM person p join organization o on p.orgId=o.id WHERE p.id = ? order by p.id}}
selects 1k rows: {{SELECT * FROM person p join organization o on p.orgId=o.id WHERE p.id between ? and ? order by p.id}}
joins full table and select 1k rows; {{SELECT * FROM person p join organization o on p.orgId=o.id order by p.id LIMIT 1000}}
- *P-P* - partitioned Person, partitioned Organization 
- *P-R* - replicated Person, partitioned Organization 

Results (ops / sec):

|| || master P-P || 4150 P-P  || master P-R || 4150 P-R ||
|  1 row | 18.69       | 19.51       | 19.13       | 19.28     |
| 1k rows | 16.34       | 15.86       | 16.40       | 16.43     |
| all table join 1k rows | 1.92        | 2.09        |  1.80       | 2.15      |


was (Author: tledkov-gridgain):
Simple join benchmark configuration:
- standalone Ignite node, queries run locally;
- tables:
{{CREATE TABLE person (id long, orgId long, name varchar, PRIMARY KEY (id, orgId))}}
{{CREATE TABLE organization (id long primary key, name varchar)}}
- Person contains 100K rows, Organization - 10K rows;
- queries:
selects 1 row: {{SELECT * FROM person p join organization o on p.orgId=o.id WHERE p.id = ? order by p.id}}
selects 1k rows: {{SELECT * FROM person p join organization o on p.orgId=o.id WHERE p.id between ? and ? order by p.id}}
joins full table and select 1k rows; {{SELECT * FROM person p join organization o on p.orgId=o.id order by p.id LIMIT 1000}}
- *P-P* - partitioned Person, partitioned Organization 
- *P-R* - replicated Person, partitioned Organization 

Results (ops / sec):

|| || master P-P || 4150 P-P  || master P-R || 4150 P-R ||
|  1 row | 18.69       | 19.51       | 19.13       | 19.28     |
| 1k rows | 16.34       | 15.86       | 16.40       | 16.43     |
| all table join 1k rows | 1.92        | 2.09        |  1.80       | 2.15      |

> B-Tree index cannot be used efficiently with IN clause.
> -------------------------------------------------------
>
>                 Key: IGNITE-4150
>                 URL: https://issues.apache.org/jira/browse/IGNITE-4150
>             Project: Ignite
>          Issue Type: Task
>          Components: sql
>    Affects Versions: 1.7
>            Reporter: Vladimir Ozerov
>            Assignee: Taras Ledkov
>            Priority: Major
>              Labels: performance
>             Fix For: 2.7
>
>
> Consider the following query:
> {code}
> SELECT * FROM table
> WHERE a = ? AND b IN (?, ?)
> {code}
> If there is an index {{(a, b)}}, it will not be used properly: only column {{a}} will be used. This will leads to multiple unnecessary comparisons.
> Most obvious way to fix that - use temporary table and {{JOIN}}. However, this approach doesn't work well when there are multiple {{IN}}'s. 
> Proper solution would be to hack deeper into H2.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)