Re: Performance-improvement idea: shortcircuit unique-index checks

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Seems a better solution would be to put a 'deleted' bit in the index so
> > we would have to visit those heap tuples only once for a committed
> > status. Similar to what we do with heap tuples so we don't have to
> > visit pg_log repeatedly.
>
> That's only a partial solution, since the index is still going to have
> to visit the row's existing tuple (which is, by definition, not yet
> committed dead). My point is that the index scanning done for
> uniqueness checks can be eliminated *entirely* for one pretty-common
> case.

I see.

>
> A deleted bit in index entries might be useful too, but I think it
> attacks a different set of cases.

Yes. Let me add some TODO items:

* Add deleted bit to index tuples to reduce heap access
* Prevent index uniqueness checks when UPDATE does not modify column

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026