opt: Poor row count estimate for join with multiple conditions

[andy-kimball]

Using the TPC-H schema, I was running a query similar to the following:

explain (opt,types) select count(*) from partsupp, lineitem where ps_partkey = l_partkey AND ps_suppkey = l_suppkey;

EXPECTED: I expected the estimated number of rows returned by the join to be in the same ballpark as the actual number of rows.

ACTUAL: The estimated number of rows was only 2,429, even though the actual number of rows is 6,001,215.

This discrepancy seems to occur only when both join conditions are used together. Either one alone gives 4+ orders of magnitude of difference, which is surprising.

Epic CRDB-16930

Jira issue: CRDB-5612

Jira issue: CRDB-13896

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
5 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!

[mgartner]

We've made many improvements to estimated row counts since this issue was created. Let's first check what the estimate row count is on the latest version and close this issue if it's more accurate.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!