Re: Index tuple deduplication limitations in pg13

On Tue, 18 Aug 2020 at 22:00, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Tue, Aug 18, 2020 at 11:52 AM Matthias van de Meent
> <matthias(dot)vandemeent(at)cofano(dot)nl> wrote:
> > Given that the above could work, the current btree tuple ordering is
> > not optimized for opclass-equal but datum image-distinct values:
> > ordering of opclass-equal values is currently determined only by tid,
> > with as an example current ordering ['0.0', '0', '0.00', '0', '0.0',
> > '0']. It would be more optimized for deduplication if that was stored
> > as e.g. ['0', '0', '0', '0.0', '0.0', '0.00'], which is why I
> > suggested to add an ordering by the datum image before the tid
> > ordering. Additionally, this extra ordering also prevents the problem
> > of [0] by never attempting an insertion of non-equal image datums in a
> > posting list of otherwise equal values, as it would be ordered either
> > before or after the posting list, never inside the list.
>
> Yeah, that would work, but at the cost of making numeric totally
> unusable. Now you cannot rely on unique enforcement detecting that '0'
> is a duplicate of '0.0'. In fact, even the most trivial use of the =
> operator will be broken in the presence of different display scales.
> It's a non-starter.

Would this extra ordering not effectively be an extra tiebreaker in
the ordering, applied before the TID? I do not know the full
implications of that, but I believe that would not result in the
limitations that you are mentioning.

- Matthias