Underestimated row count for rank window function

[msirek]

Describe the problem
A query with a predicate rank_window_function_result = 1 is returning an estimate of 1 row, but the answer should be taking into account the number of distinct values in the PARTITION BY columns and ORDER BY columns. If there are subsequent joins in the query, a sub-optimal join plan may be picked to evaluate those joins due to the bad estimate.

To Reproduce

create table t1 (a int, b int, c int not null, primary key(a));
insert into t1 select g,g,g FROM generate_series(1,100000) g(g);
analyze t1;
EXPLAIN SELECT * FROM 
  (SELECT t1.*, rank() OVER (PARTITION BY a ORDER BY b) AS rank_ab FROM t1) 
  AS ab WHERE rank_ab = 1;
                                             info
----------------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • filter
  │ estimated row count: 1
  │ filter: rank_ab = 1
  │
  └── • window
      │ estimated row count: 100,000
      │
      └── • scan
            estimated row count: 100,000 (100% of the table; stats collected 15 minutes ago)
            table: t1@t1_pkey
            spans: FULL SCAN

The esimated row count after the filter is 1, but it should be 100000.

Jira issue: CRDB-16087

[msirek]

This is related to support issue #1609

[yuzefovich]

Nice find! I'd guess that other window functions like row_number, dense_rank, and maybe others also have the same problem.