[ASTERIXDB-3462] Incorrect cardinality estimation with multicolumn PK-PK self joins

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: COMP - Compiler
• Labels: triaged
• Assignee: Vijay Sarathy
• Reporter: Vijay Sarathy
• Created: 16/Jul/24 22:43
• Updated: 07/Aug/24 14:39
• Resolved: 24/Jul/24 13:32

Description

Consider the orders collection in CH2 which has (o_id, o_w_id, and o_d_id) as the composite PK. The following query,

select count(*) from orders x, orders y
 where x.o_id = y.o_id AND y.o_w_id = x.o_w_id AND y.o_d_id = x.o_d_id

The overall cardinality of this should be the cardinality of orders since the join is a PK-PK join. However, we underestimate this cardinality as we do not recognize the composite PK.