Hi,

It is really good point. I haven't had a chance to test. But, deduplication feature of B-tree indexes introduced with PostgreSQL 13 can make it very effective. Otherwise, it is worth to try using GIN index on such columns with small number of distinct values.

19.04.2022, 21:18, "Rob Sargent" <robjsargent@gmail.com>:

On 4/19/22 11:03, aditya desai wrote:

Thanks! Apologies! Looks like I was having issues with NULL values and UNIQUE index on user defined column.

On Tue, Apr 19, 2022 at 10:00 PM Christophe Pettus <xof@thebuild.com> wrote:

> On Apr 19, 2022, at 09:27, aditya desai <admad123@gmail.com> wrote:
>
> Hi,
> Is there any way to create index on user defined type in Postgresql? Need to create index on bug_status in bug table.
>
> CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
>
> CREATE TABLE bug (
> id serial,
> description text,
> status bug_status
> );

It works right out of the box:

xof=# CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
CREATE TYPE
xof=# CREATE TABLE bug (
id serial,
description text,
status bug_status
);
CREATE TABLE
xof=# create index on bug(status);
CREATE INDEX
xof=#