[SPARK-23757] [Performance] Star schema detection improvements

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Ioana Delaney
• Created: 20/Mar/18 19:46
• Updated: 29/Mar/20 02:37

Description

Star schema consists of one or more fact tables referencing a number of dimension tables. Queries against star schema are expected to run fast because of the established RI constraints among the tables. In general, star schema joins are detected using the following conditions:

1. RI constraints (reliable detection)

• Dimension contains a primary key that is being joined to the fact table.
• Fact table contains foreign keys referencing multiple dimension tables.

2. Cardinality based heuristics

• Usually, the table with the highest cardinality is the fact table.

Existing SPARK-17791 uses a combination of the above two conditions to detect and optimize star joins. With support for informational RI constraints, the algorithm in SPARK-17791 can be improved with reliable RI detection.