Re: Indirect indexes

On 19 October 2016 at 18:40, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Oct 19, 2016 at 07:23:28PM +0530, Pavan Deolasee wrote:
>> 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.
>
> I personally would like to see how far we get with WARM before adding
> this feature that requires a DBA to evaluate and enable it.

Assuming WARM is accepted, that *might* be fine.

What we should ask is what is the difference between indirect indexes
and WARM and to what extent they overlap.

My current understanding is that WARM won't help you if you update
parts of a JSON document and/or use GIN indexes, but is effective
without needing to add a new index type and will be faster for
retrieval than indirect indexes.

So everybody please chirp in with benefits or comparisons.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services