From: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
---|---|
To: | Claudio Freire <klaussfreire(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Indirect indexes |
Date: | 2016-10-18 20:48:53 |
Message-ID: | CANP8+j+Y_fpEX5hoTfUBM52GX=tOHSUVMZTr2Onoxb=nt=uy0A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 18 October 2016 at 22:04, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Tue, Oct 18, 2016 at 3:28 PM, Alvaro Herrera
> <alvherre(at)2ndquadrant(dot)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.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2016-10-18 21:21:08 | Re: Indirect indexes |
Previous Message | Andres Freund | 2016-10-18 20:37:46 | Re: Remove vacuum_defer_cleanup_age |