Re: LP_DEAD hinting and not holding on to a buffer pin on leaf page (Was: [WIP] Zipfian distribution in pgbench)

Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>In Alik's workload, there are two queries: One UPDATE, one SELECT. Even
>though the bloated index was a unique index, and so still gets
>_bt_check_unique() item killing, the regression is still going to block
>LP_DEAD cleanup by the SELECTs, which seems like it might be quite
>important there. After all, _bt_check_unique() cleanup has to happen
>with an exclusive buffer lock held, whereas the kill_prior_tuple stuff
>happens with only a shared buffer lock held. It's not hard to imaging
>that there will be a whole lot less LP_DEAD setting, overall.

Actually, there is a much bigger reason why SELECT statement LP_DEAD
killing matters more to Alik's workload than _bt_check_unique() LP_DEAD
killing: The UPDATE query itself does not affect indexed columns. Most
UPDATEs are actually HOT-safe (despite the degree of index bloat we
see), and so most UPDATEs will never reach _bt_check_unique().

--
Peter Geoghegan