[ 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)