[IGNITE-13580] Calcite integration. Perform of JOIN 2 partitioned tables to a replicated.

• Type: Improvement
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Component/s: sql
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Iurii Gerzhedovich
• Created: 14/Oct/20 09:51
• Updated: 14/Oct/20 09:51

Description

As of now H2 based engine has a performance issue for case joining 2 partitioned tables to a replicated table. Calcite engine should choose a more optimal plan and perform such query faster

As example:

We have 3 tables:
P1 (PARTITIONED) - 100M rows
P2 (PARTITIONED) - 200K rows
R (REPLICATED) - 80 rows

SELECT count
FROM P1,
P2,
R
WHERE P1.BATCHKEY = R.BATCHKEY
AND P1.TIDENTIFIER = P2.TIDENTIFIER
AND P1.TVER = P2.TVER
AND P2.BOOK = 'CONST'
AND R.ISLATEST = TRUE