Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> writes:
> But does Postgresql visit the older tuples first moving to the newer ones,
> or the newer ones first?
It's going to visit them *all*. Reordering won't improve the
performance.
FWIW I think that with the present implementation of btree, the newer
tuples actually will be visited first --- when inserting a duplicate
key, the new entry will be inserted to the left of the equal key(s)
already present. But it doesn't matter. The only way to speed this
up is to eliminate some of the visitings, which requires keeping more
info in the index than we presently do.
regards, tom lane