Re: _bt_check_unique checks every row in table when doing update??

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:

> Mats Lofkvist <mal(at)algonet(dot)se> writes:
> > tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> >> 7.3 will be smarter about this.
>
> > Seems like I get the same behaviour with 7.3 beta1, updating
> > the same row ~20k times and then 1k times more with profiling
> > enabled (and with no vacuum in between) gives:
>
> > 2.72 166.12 1002/1002 _bt_doinsert [17]
> > [18] 53.7 2.72 166.12 1002 _bt_check_unique [18]
> > 15.81 149.01 21721926/21721926 _bt_isequal [19]
> > 0.05 1.00 221414/412979 _bt_getbuf [40]
> > 0.01 0.21 221414/409772 _bt_relbuf [91]
> > 0.01 0.02 2709/6241 heap_fetch [187]
>
> Yeah, but you'll notice there is no heap_fetch for most of 'em, unlike
> before...

Ah, missed that one.

>
> The loop in _bt_check_unique tests _bt_isequal before checking
> ItemIdDeleted, so the short-circuit for deleted items doesn't serve to
> reduce the number of key comparisons, only the number of heap tuple
> fetches. I do not think that reversing this logic would be a net
> improvement in typical cases: we want to fall out of the loop as soon as
> we've run off the set of equal keys, whether the current index entry is
> dead or alive. If we switched the test order then we'd not get out of
> the loop until we found a live entry that's bigger than the insertion
> key.

In my case the problem is that my unique index consists of three
varchar(32) columns which I suppose is making the comparison rather
expensive. But I agree that it is not obvious that changing the
logic would be an improvement in general.

>
> > (In my case, I think the call to _bt_check_unique could be
> > avoided altogether since the update isn't changing any of
> > the columns present in the unique key.
>
> It's fairly difficult for the index AM to know that; in general we don't
> have access to the old tuple to check, at the time the index update is
> running.

But you really shouldn't need the old tuple to know this since none
of the columns present in the unique index are 'set' by the update?

I.e. the 'not changing the unique key part' is not data dependent,
it is guarantied by the form of the update statement. Isn't it a
very common case to do updates with sets only on non-key columns
(only selecting on the key columns) ?

>
> regards, tom lane

_
Mats Lofkvist
mal(at)algonet(dot)se