On Tue, May 21, 2019 at 11:27 AM Walter Smith <walter@xxxxxxxxxxxx> wrote: > Very low. Probably less than ten over all time. I suspect the only use of the index is to rapidly find the processed=false rows, so the notifiable_type value isn’t important, really. It would probably work just as well on any other column. This problem has been fixed in Postgres 12, which treats heap TID as a tiebreaker column within B-Tree indexes. It sounds like you have the right idea about how to work around the problem. VACUUM will need to kill tuples in random locations in the low cardinality index, since the order of tuples is unspecified between duplicate tuples -- it is more or less random. VACUUM will tend to dirty far more pages than is truly necessary in this scenario, because there is no natural temporal locality that concentrates dead tuples in one or two particular places in the index. This has a far more noticeable impact on VACUUM duration than you might expect, since autovacuum is throttled by delays that vary according to how many pages were dirtied (and other such factors). -- Peter Geoghegan
