Re: Indirect indexes

On Wed, Oct 19, 2016 at 3:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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