Re: support create index on virtual generated column.

On Tue, Jul 22, 2025 at 4:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
> > I'm interested in this feature, specifically whether the optimizer uses
> the
> > index in situations where the expression is used rather than the virtual
> > column name.
>
> Hmm, I kinda think we should not do this. The entire point of a
> virtual column is that its values are not stored and so you can
> (for example) change the generation expression "for free".
> If it's referenced in an index that advantage goes out the window
> because we'll have to rebuild the index.
>

Don't we already have dependencies on altering a column if it's indexed?
Wouldn't that be all the barrier we'd need?

>
> Besides, this does nothing you haven't been able to do for
> decades with expression indexes.
>

What I'm hoping for with this feature is a smoother transition when people
start introducing virtual columns. If there was already an index on that
expression, and some queries start using the new virtual column (with the
same expression), will that cause those queries to miss the index we
already have? If it doesn't, then a customer can roll out the query changes
at will and not need to do some cut-over from using the expression to using
the virtual column.