On Tue, Jan 18, 2022 at 10:13 AM Mladen Gogala <gogala.mladen@xxxxxxxxx> 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)
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.
