From: Petr Jelinek <petr@2ndquadrant.com>
To: Bruce Momjian <bruce@momjian.us>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Pavan Deolasee <pavan.deolasee@gmail.com>
Cc: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Indirect indexes
Date: Thu, 20 Oct 2016 17:14:51 +0200
Message-ID: <13500012-c20a-1fc4-5a57-054c9aeb4e25@2ndquadrant.com> (raw)
In-Reply-To: <20161020122937.GA28621@momjian.us>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: petr@2ndquadrant.com, bruce@momjian.us, simon@2ndquadrant.com, pavan.deolasee@gmail.com, a.korotkov@postgrespro.ru, robertmhaas@gmail.com, alvherre@2ndquadrant.com
Subject: Re: Indirect indexes
In-Reply-To: <13500012-c20a-1fc4-5a57-054c9aeb4e25@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox