Re: Indirect indexes

On Thu, Oct 20, 2016 at 4:24 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Oct 20, 2016 at 05:14:51PM +0200, Petr Jelinek wrote:
> > > 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.
>
> Do you have to resort to extreme statements to make your point? The use
> of indexes is clear to most users, while the use of indirect indexes
> would not be, as I stated earlier.
>

It's not that difficult to explain I think. We just tell them (to
non-sophisticated users) that they are similar to the non-clustered indexes
that other dbms have (SQL Server, MySQL), which add the PK columns to the
non-clustered index when the table is clustered. Same way as there, the
index doesn't need update when the columns or the PK isn't updated.
So we have the same benefit, except that we have the feature for our heap
tables.

I think it's the same for any other feature that is added (partial indexes,
cubes, new syntax like LATERAL and FILTER). People will learn and start to
use it. We can't expect it to be used by everyone the day it's released.

>
> > 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.
>
> We don't know enough about the limits of WARM to say it is limited to
> 50%.
>
>
>