[IGNITE-4150] B-Tree index cannot be used efficiently with IN clause.

• Type: Task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.7
• Fix Version/s: 2.7
• Component/s: sql
• Labels: performance
• Ignite Flags: Docs Required
• Assignee: Taras Ledkov
• Reporter: Vladimir Ozerov
• Created: 28/Oct/16 13:17
• Updated: 26/Sep/18 08:10
• Resolved: 26/Sep/18 08:08

Description

Consider the following query:

SELECT * FROM table
WHERE a = ? AND b IN (?, ?)

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.

Issue Links

• blocks: IGNITE-8639 Query with a lot of nesting causes NPE in org.h2.util.StringUtils.indent
• is depended upon by: IGNITE-8716 SQL query and WITH caluse produces syntax error
• is duplicated by: IGNITE-6633 Repair basic SQL functionality
• is related to: IGNITE-9696 SQL: Remove documentation about bad IN performance
• relates to: IGNITE-6085 SQL: JOIN with multiple conditions is extremely slow
• links to: GitHub Pull Request #3956
• links to: Tests results