[ASTERIXDB-3470] CBO estimates incorrect join cardinality with IMDb datasets

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: COMP - Compiler
• Labels: triaged
• Assignee: murali krishna
• Reporter: Mehnaz Tabassum Mahin
• Created: 28/Jul/24 00:43
• Updated: 16/Aug/24 21:25

Description

The join cardinalities with IMDb datasets are much overestimated and the inaccuracy increases with the increase in sample sizes.

IMDb dataset: http://homepages.cwi.nl/~boncz/job/imdb.tgz  (Please change the corresponding CSV file name to "keywords.csv")

In the attachment, two 10-datasets join queries and the relevant DDL statements are given. Both join queries are with 10 IMDb datasets and different selectivity predicates.

1. The actual cardinality of the first join query is 1298, where the estimated ones are:
   • with "low" sample: 7.489 x 10¹⁰
   • with "medium" sample: 5.619 x 10¹²
   • with "high" sample: 6.022 x 10¹²
2. The actual cardinality of the second one is 1062, whereas the estimated ones are:
   • with "low" sample: 4.33 x 10⁸
   • with "medium" sample: 1.479 x 10¹¹
   • with "high" sample: 1.93 x 10¹¹