Postgres Pro
Downloads
Home   >   mailing lists

Re: Unique constraint blues - Mailing list pgsql-performance

From David G. Johnston
Subject Re: Unique constraint blues
Date
Msg-id CAKFQuwa=QN7GVNY1X=9Ou5HwvpiRXZSqySb-6o7kHJe7d6ezbg@mail.gmail.com
Whole thread Raw
In response to Unique constraint blues  (Mladen Gogala <gogala.mladen@gmail.com>)
List pgsql-performance
Tree view
On Tue, Jan 18, 2022 at 10:13 AM Mladen Gogala <gogala.mladen@gmail.com> wrote:

mgogala=# create unique index test1_uq on test1(col1,coalesce(col2,'***
EMPTY ***'));

    ->  Bitmap Index Scan on test1_uq  (cost=0.00..1.70 rows=6 width=0) 
         .......
               Index Cond: (test1.col1 = 1)

How come that the index is used for search without the "coalesce"
function?

Only the second column is an expression.  The first (leading) column is perfectly usable all by itself.  It is less efficient, hence the parent node's:

    Recheck Cond: (test1.col1 = 1)
    Filter: ((test1.col2)::text = 'test1'::text)

but usable.

If you are willing to create partial unique indexes you probably should just create two of them.  One where col2 is null and one where it isn't.

If the coalesce version is acceptable you should consider declaring the column not null and put the sentinel value directly into the record.

David J.

pgsql-performance by date:

Previous
From: Mladen Gogala
Date:
Subject: Unique constraint blues
Next
From: Ludwig Isaac Lim
Date:
Subject: PostgreSQL 12.8 Same Query Same Execution Plan Different Time