[IGNITE-6085] SQL: JOIN with multiple conditions is extremely slow

• Type: Task
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 2.1
• Component/s: sql
• Labels: performance
• Assignee: Unassigned
• Reporter: Vladimir Ozerov
• Created: 16/Aug/17 13:47
• Updated: 27/Sep/19 11:47
• Resolved: 06/May/19 12:26

Description

Consider the following query:

SELECT ... FROM A a
    INNER JOIN B b ON b.id = a.foreign_id1 OR b.id = a.foreign_id2

In this case H2 cannot use indexes on foreign_id1 or foreign_id2 columns and query execution takes extraordinary time. Known workaround for a problem is to apply multiple JOINs, e.g.:

SELECT ... FROM A a
    LEFT OUTER JOIN B b1 ON b1.id = a.foreign_id1 
    LEFT OUTER JOIN B b2 ON b2.id = a.foreign_id2
WHERE b1.id IS NOT NULL AND b2.id IS NOT NULL

On a single real-world scenario it improved exeution time by a factor of 500 (from 4s to 80ms).

Something is terribly wrong here. Probably, H2 cannot perform necessary query re-write, or cannot understand how to use index. Let's find a way to fix that.

Issue Links

• is duplicated by: IGNITE-6633 Repair basic SQL functionality
• is related to: IGNITE-4150 B-Tree index cannot be used efficiently with IN clause.
• mentioned in: Page Loading...