Re: Non-Stored Generated Columns

On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> Honestly, I'm not sure why supporting the non-stored variant of generated
> columns is so controversial...
>  
> > I am sure there are some use cases for "virtual" generated columns, and
> > I don't think that a patch that tries to implement them will be rejected
> > out of hand.  It's just that I don't think it is a very important feature.
>
> Fair enough. And a reaction I expected when I first posted.
> The outright rejection of it ever being useful, that's what surprised me.

I don't think anybody rejected the idea unconditionally.

But there is always a trade-off between the maintenance burden and the
usefulness of a feature. All that was said is that the usefulness is low.

> > You could use conditional indexes, but then you have to make sure that
> > the optimizer knows it can use these indexes.
>
> I'm not following. Are you saying the planner is not good at that on its own?
> I need to do something from my end???

I wasn't sure, but now I tested: a conditional index can also be used
by a cascading delete or update. So that's not a worry.

> > The sum of the sizes of these indexes shouldn't exceed the size of an
> > unconditional index by much, but they would still be more expensive:
> > each statement would have to look through all the indexes to decide
> > which ones it can use and which ones not.
>
> Something I maybe didn't make clear. The XArc virtual columns are never accessed.

Yes, they are. The query planner considers all indexes.

Yours,
Laurenz Albe