public inbox for [email protected]
help / color / mirror / Atom feedFrom: Samed YILDIRIM <[email protected]>
To: Rob Sargent <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Create index on user defined type
Date: Sun, 24 Apr 2022 13:43:23 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAN0SRDHgW+5-yGdLE-y6eCpWdffV7iexabGL1PmZOb7BU=QeWg@mail.gmail.com>
<[email protected]>
<CAN0SRDHH=10fPDh2tTY0cjLdTzESeaoV1YRPgk=mvxhcNqM_=w@mail.gmail.com>
<[email protected]>
<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" <[email protected]>:</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:[email protected]" rel="noopener noreferrer">[email protected]</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:[email protected]" rel="noopener noreferrer" target="_blank">[email protected]</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>
view thread (5+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Create index on user defined type
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox