Re: Indirect indexes

On Tue, Oct 18, 2016 at 5:48 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 18 October 2016 at 22:04, Claudio Freire <klaussfreire@gmail.com> wrote:
>> On Tue, Oct 18, 2016 at 3:28 PM, Alvaro Herrera
>> <alvherre@2ndquadrant.com> wrote:
>>> I propose we introduce the concept of "indirect indexes".  I have a toy
>>> implementation and before I go further with it, I'd like this assembly's
>>> input on the general direction.
>>>
>>> Indirect indexes are similar to regular indexes, except that instead of
>>> carrying a heap TID as payload, they carry the value of the table's
>>> primary key.  Because this is laid out on top of existing index support
>>> code, values indexed by the PK can only be six bytes long (the length of
>>> ItemPointerData); in other words, 281,474,976,710,656 rows are
>>> supported, which should be sufficient for most use cases.[1]
>>
>>
>> You don't need that limitation (and vacuum will be simpler) if you add
>> the PK as another key, akin to:
>>
>> CREATE INDIRECT INDEX idx ON tab (a, b, c);
>>
>> turns into
>>
>> CREATE INDEX idx ON tab (a, b, c, pk);
>>
>> And is queried appropriately (using an index-only scan, extracting the
>> PK from the index tuple, and then querying the PK index to get the
>> tids).
>>
>> In fact, I believe that can work with all index ams supporting index-only scans.
>
> But if you did that, an UPDATE of a b or c would cause a non-HOT
> update, so would defeat the purpose of indirect indexes.

I meant besides all the other work, omitting the tid from the index
(as only the PK matters), marking them indirect, and all that.