Re: Unique constraint blues

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Unique constraint blues
Дата
Msg-id CAKFQuwa=QN7GVNY1X=9Ou5HwvpiRXZSqySb-6o7kHJe7d6ezbg@mail.gmail.com
обсуждение исходный текст
Ответ на Unique constraint blues  (Mladen Gogala <gogala.mladen@gmail.com>)
Список pgsql-performance
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Mladen Gogala
Дата:
Сообщение: Unique constraint blues
Следующее
От: Ludwig Isaac Lim
Дата:
Сообщение: PostgreSQL 12.8 Same Query Same Execution Plan Different Time