Re: Allow virtual columns in index expressions or predicate

From: Chengpeng Yan <chengpeng_yan(at)Outlook(dot)com>
To: Richard Guo <guofenglinux(at)gmail(dot)com>
Cc: "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Allow virtual columns in index expressions or predicate
Date: 2025-10-03 09:10:35
Message-ID: 8D88383B-FB2C-443E-A999-930A4DCD25C8@Outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


On Sep 30, 2025, at 17:13, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:

I'm looking for a solution to support virtual generated columns in
index expressions or predicate. The comment in DefineIndex() seems to
suggest that virtual generated columns there can be expanded in
RelationGetIndexExpressions() and RelationGetIndexPredicate().

/*
* XXX Virtual generated columns in index expressions or predicates
* could be supported, but it needs support in
* RelationGetIndexExpressions() and RelationGetIndexPredicate().
*/

I'm not sure this is the appropriate approach, especially since we
don't have the heap relation available in these functions. I think
we're good as long as we expand the virtual columns before the index
build process; otherwise, evaluating these expressions during the
build will cause executor errors due to unexpanded virtual columns.

I wonder if we can do the expansion in DefineIndex(), before the call
to index_create(), like the attached.

- Richard
<v1-0001-Allow-virtual-columns-in-index-expressions-or-pre.patch>

Hi Richard.

This is a good usability improvement, making it easier to create an index when a virtual column is in an expression or a WHERE clause.

It might also be nice if this could be extended to allow creating an index directly on the virtual column, like CREATE INDEX ON tbl (virtual_col).

If we view this feature as a "syntactic sugar" to simplify creating an expression index, then I think this patch is very easy to accept.

However, if we consider more complex situations, like a user running ALTER TABLE ... SET EXPRESSION, it raises more questions. Whether we need to add the kind of full support for these cases that was discussed in [1] needs a more detailed discussion. But for the simpler goal of being a syntax improvement, this patch works well.

Thanks,

Chengpeng Yan

[1] https://www.postgresql.org/message-id/flat/CACJufxGao-cypdNhifHAdt8jHfK6-HX%3DtRBovBkgRuxw063GaA%40mail.gmail.com

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Mingli Zhang 2025-10-03 09:16:50 Re: Fix incorrect function reference BufFileOpenShared in comment.
Previous Message Daniel Gustafsson 2025-10-03 08:25:58 Re: [PATCH] Add tests for Bitmapset