<div><div>Hi,</div><div> </div><div>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.</div></div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>19.04.2022, 21:18, "Rob Sargent" <robjsargent(at)gmail(dot)com>:</div><blockquote><div>On 4/19/22 11:03, aditya desai wrote:</div><blockquote><div>Thanks! Apologies! Looks like I was having issues with NULL values and UNIQUE index on user defined column.</div> <div><div>On Tue, Apr 19, 2022 at 10:00 PM Christophe Pettus <<a href="mailto:xof(at)thebuild(dot)com" rel="noopener noreferrer">xof(at)thebuild(dot)com</a>> wrote:</div><blockquote style="border-left-color:rgb( 204 , 204 , 204 );border-left-style:solid;border-left-width:1px;margin:0px 0px 0px 0.8ex;padding-left:1ex"><br />> On Apr 19, 2022, at 09:27, aditya desai <<a href="mailto:admad123(at)gmail(dot)com" rel="noopener noreferrer" target="_blank">admad123(at)gmail(dot)com</a>> wrote:<br />><br />> Hi,<br />> Is there any way to create index on user defined type in Postgresql? Need to create index on bug_status in bug table.<br />><br />> CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');<br />><br />> CREATE TABLE bug (<br />> id serial,<br />> description text,<br />> status bug_status<br />> );<br /><br />It works right out of the box:<br /><br />xof=# CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');<br />CREATE TYPE<br />xof=# CREATE TABLE bug (<br /> id serial,<br /> description text,<br /> status bug_status<br />);<br />CREATE TABLE<br />xof=# create index on bug(status);<br />CREATE INDEX<br />xof=#<br /> </blockquote></div></blockquote><font face="Courier New, Courier, monospace">I would have to wonder at the effectiveness an index on such a small number of possible values. </font></blockquote>