Comment by hans_castorp

> Unique indexes slow inserts since db has to acquire a full table lock

An INSERT never results in a full table lock (as in "the lock would prevent other inserts or selects on the table)

Any expression used in the WHERE clause that isn't indexed will probably result in a Seq Scan. CASE expressions are no different than e.g. a function call regarding this.

A stable function marked as "STABLE" (or even immutable) can be optimized differently (e.g. can be "inlined"), so yes that's a good recommendation.