This question (and its variants) show quite often on #postgresql on IRC. People get sequential scans, and are worried that it's slow and bad.
So, I hope that this blogpost will shed some light on the subject why indexes are being chosen to be used, or not.
Today, Mattias|farm on IRC asked how to create primary key using HASH index. After some talk, he said that in some books it said that for “=" (equality) hash indexes are better.
So, I digged a bit deeper.
More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.
Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.
So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?
On 1st of August Tom Lane committed patch from Itagaki Takahiro:
Improve unique-constraint-violation error messages to include the exact values being complained of. In passing, also remove the arbitrary length limitation in the similar error detail message for foreign key violations. Itagaki Takahiro
and later added an extension to it:
Department of second thoughts: let's show the exact key during unique index build failures, too. Refactor a bit more since that error message isn't spelled the same.
Continue reading Waiting for 8.5 – better messages for unique violation