Subject: Re: Indirect indexes
To: Bruce Momjian <bruce@momjian.us>, Simon Riggs <simon@2ndquadrant.com>
References: <20161018182843.xczrxsa2yd47pnru@alvherre.pgsql>
 <CA+TgmoajGNsjTg7B3KjdpuDEuaOiMXWoh6ewc9jkhuCuv2deFQ@mail.gmail.com>
 <CAPpHfdseka0Vmukp9SRKgGDdGb7pYDcn-iWGqFv1a0R9em2anQ@mail.gmail.com>
 <CABOikdM2FpKs813oxfCXeAYR5SiK8V9-x8o7Y1zZpD_-Oso7BQ@mail.gmail.com>
 <20161019164043.GN5087@momjian.us>
 <CANP8+j+ERoVPn6gFevWqm6U=SGAeg3JhCASpLDLysKnUeViujg@mail.gmail.com>
 <20161019170416.GR5087@momjian.us> <20161020122937.GA28621@momjian.us>
Cc: Pavan Deolasee <pavan.deolasee@gmail.com>,
 Alexander Korotkov <a.korotkov@postgrespro.ru>,
 Robert Haas <robertmhaas@gmail.com>,
 Alvaro Herrera <alvherre@2ndquadrant.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
From: Petr Jelinek <petr@2ndquadrant.com>
Message-ID: <13500012-c20a-1fc4-5a57-054c9aeb4e25@2ndquadrant.com>
Date: Thu, 20 Oct 2016 17:14:51 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.3.0
MIME-Version: 1.0
In-Reply-To: <20161020122937.GA28621@momjian.us>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 20/10/16 14:29, Bruce Momjian wrote:
> On Wed, Oct 19, 2016 at 01:04:16PM -0400, Bruce Momjian wrote:
>> On Wed, Oct 19, 2016 at 06:58:05PM +0200, Simon Riggs wrote:
>>>>> 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.
>>
>> First, I love WARM because everyone gets the benefits by default.  For
>> example, a feature that improves performance by 10% but is only used by
>> 1% of users has a usefulness of 0.1% --- at least that is how I think of
>> it.
> 
> Just to clarify, if a feature improves performance by 1%, but is enabled
> by default, that is 10x more useful across our entire user base as the
> feature numbers listed above, 1% vs 0.1%.
> 
> Also, it seems indirect indexes would be useful for indexing columns
> that are not updated frequently on tables that are updated frequently,
> and whose primary key is not updated frequently.  That's quite a logic
> problem for users to understand.
> 

Which covers like 99.9% of problematic cases I see on daily basis.

And by that logic we should not have indexes at all, they are not
automatically created and user needs to think about if they need them or
not.

Also helping user who does not have performance problem by 1% is very
different from helping user who has performance problem by 50% even if
she needs to think about the solution a bit.

WARM can do WARM update 50% of time, indirect index can do HOT update
100% of time (provided the column is not changed), I don't see why we
could not have both solutions.

That all being said, it would be interesting to hear Álvaro's thoughts
about which use-cases he expects indirect indexes to work better than WARM.

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers