[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16628390#comment-16628390
]
ASF GitHub Bot commented on IGNITE-4150:
Github user devozerov closed the pull request at:
https://github.com/apache/ignite/pull/3956
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16623913#comment-16623913
]
Taras Ledkov commented on IGNITE-4150:
--
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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16607195#comment-16607195
]
Taras Ledkov commented on IGNITE-4150:
--
[~vozerov], tests have been re-run OK.
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16588620#comment-16588620
]
Taras Ledkov commented on IGNITE-4150:
--
[~vozerov], the CPP & .NET tests are fixed according with SQL standard.
H2 updates change default scale of TIMESTAMP type. According with SQL standard
default scale for TIMESTAMP's nanos is 6. In previous H2 version was 9.
> 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, sql-stability
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16582250#comment-16582250
]
Vladimir Ozerov commented on IGNITE-4150:
-
[~tledkov-gridgain],
Looks like there are new failures in CPP and .NET suites related to types
conversion. We need to fix this before merge.
> 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, sql-stability
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16579920#comment-16579920
]
Vladimir Ozerov commented on IGNITE-4150:
-
One more TC before merge to master:
https://ci.ignite.apache.org/viewType.html?buildTypeId=IgniteTests24Java8_RunAllSql
> 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, sql-stability
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16525197#comment-16525197
]
Taras Ledkov commented on IGNITE-4150:
--
[~vozerov], I've fixed failures at the parser, collocation model building and
{{RECOMPILE_ALWAYS}} option
The SQL tests are OK. Please take a look.
> 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: Vladimir Ozerov
>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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16479715#comment-16479715
]
Joel Lang commented on IGNITE-4150:
---
[~vozerov] this expected fix version for this has been changed many times, do
you think this can make it into Ignite 2.6?
> 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: Vladimir Ozerov
>Priority: Major
> Labels: performance
> Fix For: 2.6
>
>
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16467348#comment-16467348
]
Vladimir Ozerov commented on IGNITE-4150:
-
Unfortunately there are two problems:
1) Some new failures in splitter due to changed model in H2
2) Changed dependency on vividsolution, which is a breaking change (artifact
name and packages were changed).
We have to move the ticket to further release.
> 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: Vladimir Ozerov
>Priority: Major
> Labels: performance
> Fix For: 2.6
>
>
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16467015#comment-16467015
]
Vladimir Ozerov commented on IGNITE-4150:
-
Test run:
https://ci.ignite.apache.org/viewLog.html?buildId=1274365&tab=buildResultsDiv&buildTypeId=IgniteTests24Java8_RunAllSql
> 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: Vladimir Ozerov
>Priority: Major
> Labels: performance
> Fix For: 2.5
>
>
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16467011#comment-16467011
]
ASF GitHub Bot commented on IGNITE-4150:
GitHub user devozerov opened a pull request:
https://github.com/apache/ignite/pull/3956
IGNITE-4150
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/gridgain/apache-ignite ignite-4150
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/ignite/pull/3956.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #3956
commit 32f2540d20223539e5a587993fa3e81d0d02961d
Author: devozerov
Date: 2018-05-08T07:16:28Z
IGNITE-4150: Updated H2 version.
> 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: Vladimir Ozerov
>Priority: Major
> Labels: performance
> Fix For: 2.5
>
>
> 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)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16247233#comment-16247233
]
Vladimir Ozerov commented on IGNITE-4150:
-
Solution to this ticket: update to latest H2 version when it is available
(greater than 1.4.196).
> 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
> Labels: performance
> Fix For: 2.4
>
>
> 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
(v6.4.14#64029)
[jira] [Commented] (IGNITE-4150) B-Tree index cannot be used efficiently with IN clause.
[
https://issues.apache.org/jira/browse/IGNITE-4150?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16247231#comment-16247231
]
Vladimir Ozerov commented on IGNITE-4150:
-
Fixed in
https://github.com/h2database/h2database/commit/7ec1dbee0ad6a34906ec172afa5645b983db3b3f
> 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
> Labels: performance
> Fix For: 2.4
>
>
> 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
(v6.4.14#64029)
