Re: Remove useless GROUP BY columns considering unique index

On Thu, Nov 28, 2024 at 2:11 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>

> I think it'll make more sense to adjust some of the
> existing tests to use a unique constraint instead of a PK and then
> adjust a column's NOT NULL property to check that part of the code is
> working correctly.
>
looking around, i inserted some tests to indexing.sql,
create_index.sql, aggregates.sql
also added tests for sort by index oid.

> Another issue with this is that the list of indexes being used is not
> sorted by Oid. If you look at RelationGetIndexList() you'll see that
> we perform a sort. That gives us more consistency in the planner. I
> think this patch should be doing that too, otherwise, you could end up
> with a plan change after some operation that changes the order that
> the indexes are stored in the pg_index table. It's probably fairly
> unlikely, but it is the sort of issue that someone will eventually
> discover and report.
>

Thanks for the tip!
I have wondered about multiple matches, simply choosing the first one
may have some surprise results.
i didn't know that in some cases we use list_sort to make the result
more deterministic.
When there are multiple matches, we need a determined way to choose which one.
so please check attached.