Re: Improve statistics estimation considering GROUP-BY as a 'uniqueiser'

Hi, Vlada.

On Tue, Feb 18, 2025 at 6:56 PM Vlada Pogozhelskaya
<v(dot)pogozhelskaya(at)postgrespro(dot)ru> wrote:
> Following the discussion on improving statistics estimation by considering GROUP BY as a unique constraint, I’ve prepared a patch that integrates GROUP BY into cardinality estimation in a similar way to DISTINCT.
>
> This patch ensures that when a subquery contains a GROUP BY clause, the optimizer recognizes the grouped columns as unique. The logic follows a straightforward approach, comparing the GROUP BY columns with the target list to determine uniqueness.
>
> I’d appreciate any feedback or suggestions for further improvements.

Thank you for your patch, but your message lacking of explanation on
what is your approach and how is it different from previously
published patches on this thread. As I get from the code, you check
if group by clauses are same to targetlist. If that's true, you
assume every column to be unique. But that's just doesn't work this
way. If values are unique in some set of columns, individual columns
might have repeats. See the example.

# select x, y from generate_series(1,3) x, generate_series(1, 3) y
group by x, y;
x | y
---+---
3 | 2
2 | 2
3 | 1
2 | 1
1 | 3
1 | 2
1 | 1
2 | 3
3 | 3
(9 rows)

x and y are unique here as a pair. But individual x and y values have repeats.

------
Regards,
Alexander Korotkov
Supabase