Re: UniqueKey v2

On Fri, Oct 20, 2023 at 4:33 PM <zhihuifan1213(at)163(dot)com> wrote:
>
>
> > i did some simple tests using text data type.
> >
> > it works with the primary key, not with unique indexes.
> > it does not work when the column is unique, not null.
> >
> > The following is my test.
>
> Can you simplify your test case please? I can't undertand what "doesn't
> work" mean here and for which case. FWIW, this feature has nothing with
> the real data, I don't think inserting any data is helpful unless I
> missed anything.

Sorry for not explaining it very well.
"make distinct as no-op."
my understanding: it means: if fewer rows meet the criteria "columnX <
const_a;" , after analyze the table, it should use index only scan
for the queryA?
--queryA:
select distinct columnX from the_table where columnX < const_a;

There are several ways for columnX to be unique: primark key, unique
key, unique key nulls distinct, unique key nulls not distinct, unique
key and not null.

After applying your patch, only the primary key case will make the
queryA explain output using the index-only scan.