Re: INSERT...ON DUPLICATE KEY IGNORE

On Sat, Aug 31, 2013 at 7:38 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>> Imo that solution is fairly elegant because it doesn't cause any heap
>> bloat, and it causes the same amount of index bloat
>> "insert-into-heap-first" type of solutions cause.
>
> I don't think that's a reasonable comparison. Bloating indexes with
> dead *duplicate* tuples is, in general, worse than doing the same with
> the heap. If you end up with a heavily bloated index with lots of
> duplicates, that will adversely affect performance worse than with
> non-duplicate index tuples (see [1] for example). That was one reason
> that HOT helped as much as it did, I believe.

Bear in mind also that one unique index could hardly ever have real
duplicates, but it would still get "broken promise"/bloat tuples
because another, later index said it had a duplicate. All of the
indexes get bloated (quite possibly) just because of a duplicate in
one. With a table with many unique indexes and many reasons to decide
to reject a tuple proposed for insertion by INSERT...ON DUPLICATE KEY
IGNORE, it isn't hard to imagine them all becoming heavily bloated
very quickly.

--
Peter Geoghegan