The VACUUM problems seem fairly serious. It's true that these indexes will be less subject to bloat, because they only need updating when the PK or the indexed columns change, not when other indexed columns change. On the other hand, there's nothing to prevent a PK from being recycled for an unrelated tuple. We can guarantee that a TID won't be recycled until all index references to the TID are gone, but there's no such guarantee for a PK. AFAICT, that would mean that an indirect index would have to be viewed as unreliable: after looking up the PK, you'd *always* have to recheck that it actually matched the index qual.
AFAICS, even without considering VACUUM, indirect indexes would be always used with recheck.
As long as they don't contain visibility information. When indirect indexed column was updated, indirect index would refer same PK with different index keys.
There is no direct link between indirect index tuple and heap tuple, only logical link using PK. Thus, you would anyway have to recheck.
Another approach would be to include visibility information into indirect indexes themselves. In this case, index should support snapshots by itself and don't produce false positives.
This approach would require way more intrusive changes in index AMs. We would probably not able to reuse same index AM and have to make a new one. But it seems like rather better design for me.
------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company