Subject: Re: UniqueKey v2

From: [email-masked]
Date: Fri, 20 Oct 2023 16:29:16 +0800

> 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. 

-- 
Best Regards
Andy Fan

---

From: jian he <[email-masked]>
Date: Mon, 23 Oct 2023 08:00:00 +0800

> the real data, I don't think inserting any data is helpful unless I

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.

---

From: [email-masked]
Date: Tue, 07 Nov 2023 11:44:42 +0800

>  const_a;" , after analyze the table, it should use index only scan

No, "mark distinct as no-op" means the distinct node can be discarded
automatically since it is not needed any more. The simplest case would
be "select distinct pk from t", where it should be same as "select pk
from t".  You can check the testcase for the more cases. 

-- 
Best Regards
Andy Fan