Re: Indirect indexes

On Wed, Oct 19, 2016 at 7:19 PM, Alexander Korotkov <
a(dot)korotkov(at)postgrespro(dot)ru> wrote:

> 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.
>
>
I agree. Also, I think the recheck mechanism will have to be something like
what I wrote for WARM i.e. only checking for index quals won't be enough
and we would actually need to verify that the heap tuple satisfies the key
in the indirect index.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services