pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH v2] Allow to collect statistics on virtual generated columns
7+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-06-15 ca6969629d 6603e81e69 293de33ae5 82140a71db Modernize pg_bsd_indent's error/warning reporting code.
* [PATCH v2] Allow to collect statistics on virtual generated columns
@ 2025-07-18 00:58 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 7+ messages in thread
From: Yugo Nagata @ 2025-07-18 00:58 UTC (permalink / raw)
During ANALYZE, generation expressions are expanded, and statistics are
computed using compute_expr_stats(). To support this, both compute_expr_stats()
and AnlExprData are now exported from extended_stats.c.
To enable the optimizer to make use of these statistics, a new field named
virtual_gencols is added to RelOptInfo. This field holds the expressions of
virtual generated columns in the table. In examine_variable(), if an
expression in a WHERE clause matches a virtual generated column, the
corresponding statistics are used for that expression.
---
doc/src/sgml/ref/alter_table.sgml | 9 +--
src/backend/commands/analyze.c | 32 ++++++----
src/backend/optimizer/util/plancat.c | 60 +++++++++++++++++++
src/backend/statistics/extended_stats.c | 11 +---
src/backend/utils/adt/selfuncs.c | 44 +++++++++++++-
src/include/nodes/pathnodes.h | 19 ++++++
.../statistics/extended_stats_internal.h | 9 +++
.../regress/expected/generated_virtual.out | 7 +++
src/test/regress/sql/generated_virtual.sql | 3 +
9 files changed, 166 insertions(+), 28 deletions(-)
inline patch 1: doc/src/sgml/ref/alter_table.sgml, src/backend/commands/analyze.c, src/backend/optimizer/util/plancat.c and 6 more (+166/-28 lines) download inline-1.patch
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..bffb07775f7 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -210,8 +210,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
@@ -275,12 +273,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- When this form is used on a stored generated column, its statistics
- are removed, so running
- <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 40d66537ad7..438f9595e8e 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -41,6 +41,7 @@
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -559,13 +560,28 @@ do_analyze_rel(Relation onerel, const VacuumParams params,
{
VacAttrStats *stats = vacattrstats[i];
AttributeOpts *aopt;
+ Form_pg_attribute attr = TupleDescAttr(onerel->rd_att, stats->tupattnum - 1);
- stats->rows = rows;
- stats->tupDesc = onerel->rd_att;
- stats->compute_stats(stats,
- std_fetch_func,
- numrows,
- totalrows);
+ /*
+ * For a virtual generated column, compute statistics for the expression value.
+ */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ AnlExprData *exprdata = (AnlExprData *) palloc0(sizeof(AnlExprData));
+
+ exprdata->expr = build_generation_expression(onerel, stats->tupattnum);
+ exprdata->vacattrstat = stats;
+ compute_expr_stats(onerel, exprdata, 1, rows, numrows);
+ }
+ else
+ {
+ stats->rows = rows;
+ stats->tupDesc = onerel->rd_att;
+ stats->compute_stats(stats,
+ std_fetch_func,
+ numrows,
+ totalrows);
+ }
/*
* If the appropriate flavor of the n_distinct option is
@@ -1049,10 +1065,6 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
if (attr->attisdropped)
return NULL;
- /* Don't analyze virtual generated columns */
- if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- return NULL;
-
/*
* Get attstattarget value. Set to -1 if null. (Analyze functions expect
* -1 to mean use default_statistics_target; see for example
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index c6a58afc5e5..68f7349ffc8 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -42,6 +42,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -77,6 +78,7 @@ static List *get_relation_constraints(PlannerInfo *root,
bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
static void set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
Relation relation);
@@ -508,6 +510,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->indexlist = indexinfos;
+ /* Make list of virtual generated columns */
+ rel->virtual_gencols = get_relation_virtual_gencols(rel, relation);
+
rel->statlist = get_relation_statistics(rel, relation);
/* Grab foreign-table info using the relcache, while we have it */
@@ -1482,6 +1487,61 @@ get_relation_constraints(PlannerInfo *root,
return result;
}
+/*
+ * get_relation_virtual_gencols
+ * Retrieve virtual generated columns defined on the table.
+ *
+ * Returns a List (possibly empty) of VirtualGeneratedColumnInfoInfo objects
+ * containing the generation expressions. Each one has been processed by
+ * eval_const_expressions(), and its Vars are changed to have the varno
+ * indicated by rel->relid. This allows the expressions to be easily
+ * compared to expressions taken from WHERE.
+ */
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation)
+{
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ Index varno = rel->relid;
+ List *virtual_gencols = NIL;
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ VirtualGeneratedColumnInfo *info;
+
+ info = makeNode(VirtualGeneratedColumnInfo);
+ info->attno = attr->attnum;
+ info->expr = build_generation_expression(relation, attr->attnum);
+
+ /*
+ * Run the expressions through eval_const_expressions. This is
+ * not just an optimization, but is necessary, because the
+ * planner will be comparing them to similarly-processed qual
+ * clauses, and may fail to detect valid matches without this.
+ * We must not use canonicalize_qual, however, since these
+ * aren't qual expressions.
+ */
+ info->expr = eval_const_expressions(NULL, info->expr);
+
+ /* May as well fix opfuncids too */
+ fix_opfuncids(info->expr);
+
+ /* Fix Vars to have the desired varno */
+ if (varno != 1)
+ ChangeVarNodes((Node *) info->expr, 1, varno, 0);
+
+ virtual_gencols = lappend(virtual_gencols, info);
+ }
+ }
+ }
+
+ return virtual_gencols;
+}
+
/*
* Try loading data for the statistics object.
*
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index a8b63ec0884..e26352cacec 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -82,15 +82,6 @@ static void statext_store(Oid statOid, bool inh,
static int statext_compute_stattarget(int stattarget,
int nattrs, VacAttrStats **stats);
-/* Information needed to analyze a single simple expression. */
-typedef struct AnlExprData
-{
- Node *expr; /* expression to analyze */
- VacAttrStats *vacattrstat; /* statistics attrs to analyze */
-} AnlExprData;
-
-static void compute_expr_stats(Relation onerel, AnlExprData *exprdata,
- int nexprs, HeapTuple *rows, int numrows);
static Datum serialize_expr_stats(AnlExprData *exprdata, int nexprs);
static Datum expr_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
static AnlExprData *build_expr_data(List *exprs, int stattarget);
@@ -2103,7 +2094,7 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp,
/*
* Compute statistics about expressions of a relation.
*/
-static void
+void
compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
HeapTuple *rows, int numrows)
{
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 17fbfa9b410..00e3972ff4f 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5408,6 +5408,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
*/
ListCell *ilist;
ListCell *slist;
+ ListCell *vlist;
Oid userid;
/*
@@ -5576,6 +5577,46 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
break;
}
+ /*
+ * Search virtual generated columns for one with a matching expression
+ * and use the statistics collected for it if we have.
+ */
+ foreach(vlist, onerel->virtual_gencols)
+ {
+ VirtualGeneratedColumnInfo *info = (VirtualGeneratedColumnInfo *) lfirst(vlist);
+ Node *expr = info->expr;
+
+ /*
+ * Stop once we've found statistics for the expression (either
+ * for a virtual generated columns or an index in the preceding
+ * loop).
+ */
+ if (vardata->statsTuple)
+ break;
+
+ /* strip RelabelType before comparing it */
+ if (expr && IsA(expr, RelabelType))
+ expr = (Node *) ((RelabelType *) expr)->arg;
+
+ if (equal(node, expr))
+ {
+ Var *var = makeVar(onerel->relid,
+ info->attno,
+ vardata->atttype,
+ vardata->atttypmod,
+ exprCollation(node),
+ 0);
+ /*
+ * There cannot be a unique constraint on a virtual generated column.
+ * Other fields other than the stats tuple must be already set.
+ */
+ vardata->isunique = false;
+
+ /* Try to locate some stats */
+ examine_simple_variable(root, var, vardata);
+ }
+ }
+
/*
* Search extended statistics for one with a matching expression.
* There might be multiple ones, so just grab the first one. In the
@@ -5591,7 +5632,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
/*
* Stop once we've found statistics for the expression (either
- * from extended stats, or for an index in the preceding loop).
+ * from extended stats, or for an index or a virtual generated
+ * column in the preceding loop).
*/
if (vardata->statsTuple)
break;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index ad2726f026f..433b7cc6d50 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -970,6 +970,8 @@ typedef struct RelOptInfo
List *indexlist;
/* list of StatisticExtInfo */
List *statlist;
+ /* list of VirtualGeneratedColumnInfo */
+ List *virtual_gencols;
/* size estimates derived from pg_class */
BlockNumber pages;
Cardinality tuples;
@@ -1322,6 +1324,23 @@ typedef struct StatisticExtInfo
List *exprs;
} StatisticExtInfo;
+/*
+ * VirtualGeneratedColumnInfo
+ * Information about virtual generated columns for planning/optimization
+ */
+typedef struct VirtualGeneratedColumnInfo
+{
+ pg_node_attr(no_copy_equal, no_read, no_query_jumble)
+
+ NodeTag type;
+
+ /* attribute number of virtual generated column */
+ AttrNumber attno;
+
+ /* generation expression */
+ Node *expr;
+} VirtualGeneratedColumnInfo;
+
/*
* JoinDomains
*
diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h
index efcb7dc3546..8eaea3b7566 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -68,6 +68,12 @@ typedef struct StatsBuildData
bool **nulls;
} StatsBuildData;
+/* Information needed to analyze a single simple expression. */
+typedef struct AnlExprData
+{
+ Node *expr; /* expression to analyze */
+ VacAttrStats *vacattrstat; /* statistics attrs to analyze */
+} AnlExprData;
extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data);
extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct);
@@ -127,4 +133,7 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root,
Selectivity *overlap_basesel,
Selectivity *totalsel);
+extern void
+compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
+ HeapTuple *rows, int numrows);
#endif /* EXTENDED_STATS_INTERNAL_H */
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..4469206dab0 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1493,6 +1493,13 @@ create table gtest32 (
);
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+ count
+-------
+ 5
+(1 row)
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..523ab188dac 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,6 +817,9 @@ create table gtest32 (
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
--
2.43.0
* [PATCH v3] Allow to collect statistics on virtual generated columns
@ 2025-07-18 00:58 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 7+ messages in thread
From: Yugo Nagata @ 2025-07-18 00:58 UTC (permalink / raw)
During ANALYZE, generation expressions are expanded, and statistics are
computed using compute_expr_stats(). To support this, both compute_expr_stats()
and AnlExprData are now exported from extended_stats.c.
To enable the optimizer to make use of these statistics, a new field named
virtual_gencols is added to RelOptInfo. This field holds the expressions of
virtual generated columns in the table. In examine_variable(), if an
expression in a WHERE clause matches a virtual generated column, the
corresponding statistics are used for that expression.
---
doc/src/sgml/ref/alter_table.sgml | 9 +--
src/backend/commands/analyze.c | 32 ++++++----
src/backend/optimizer/util/plancat.c | 60 +++++++++++++++++++
src/backend/statistics/extended_stats.c | 11 +---
src/backend/utils/adt/selfuncs.c | 44 +++++++++++++-
src/include/nodes/pathnodes.h | 19 ++++++
.../statistics/extended_stats_internal.h | 9 +++
.../regress/expected/generated_virtual.out | 7 +++
src/test/regress/sql/generated_virtual.sql | 3 +
9 files changed, 166 insertions(+), 28 deletions(-)
inline patch 1: doc/src/sgml/ref/alter_table.sgml, src/backend/commands/analyze.c, src/backend/optimizer/util/plancat.c and 6 more (+166/-28 lines) download inline-1.patch
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..bffb07775f7 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -210,8 +210,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
@@ -275,12 +273,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- When this form is used on a stored generated column, its statistics
- are removed, so running
- <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 40d66537ad7..438f9595e8e 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -41,6 +41,7 @@
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -559,13 +560,28 @@ do_analyze_rel(Relation onerel, const VacuumParams params,
{
VacAttrStats *stats = vacattrstats[i];
AttributeOpts *aopt;
+ Form_pg_attribute attr = TupleDescAttr(onerel->rd_att, stats->tupattnum - 1);
- stats->rows = rows;
- stats->tupDesc = onerel->rd_att;
- stats->compute_stats(stats,
- std_fetch_func,
- numrows,
- totalrows);
+ /*
+ * For a virtual generated column, compute statistics for the expression value.
+ */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ AnlExprData *exprdata = (AnlExprData *) palloc0(sizeof(AnlExprData));
+
+ exprdata->expr = build_generation_expression(onerel, stats->tupattnum);
+ exprdata->vacattrstat = stats;
+ compute_expr_stats(onerel, exprdata, 1, rows, numrows);
+ }
+ else
+ {
+ stats->rows = rows;
+ stats->tupDesc = onerel->rd_att;
+ stats->compute_stats(stats,
+ std_fetch_func,
+ numrows,
+ totalrows);
+ }
/*
* If the appropriate flavor of the n_distinct option is
@@ -1049,10 +1065,6 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
if (attr->attisdropped)
return NULL;
- /* Don't analyze virtual generated columns */
- if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- return NULL;
-
/*
* Get attstattarget value. Set to -1 if null. (Analyze functions expect
* -1 to mean use default_statistics_target; see for example
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 6ce4efea118..db78e1b639a 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -42,6 +42,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -77,6 +78,7 @@ static List *get_relation_constraints(PlannerInfo *root,
bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
static void set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
Relation relation);
@@ -508,6 +510,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->indexlist = indexinfos;
+ /* Make list of virtual generated columns */
+ rel->virtual_gencols = get_relation_virtual_gencols(rel, relation);
+
rel->statlist = get_relation_statistics(rel, relation);
/* Grab foreign-table info using the relcache, while we have it */
@@ -1482,6 +1487,61 @@ get_relation_constraints(PlannerInfo *root,
return result;
}
+/*
+ * get_relation_virtual_gencols
+ * Retrieve virtual generated columns defined on the table.
+ *
+ * Returns a List (possibly empty) of VirtualGeneratedColumnInfoInfo objects
+ * containing the generation expressions. Each one has been processed by
+ * eval_const_expressions(), and its Vars are changed to have the varno
+ * indicated by rel->relid. This allows the expressions to be easily
+ * compared to expressions taken from WHERE.
+ */
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation)
+{
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ Index varno = rel->relid;
+ List *virtual_gencols = NIL;
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ VirtualGeneratedColumnInfo *info;
+
+ info = makeNode(VirtualGeneratedColumnInfo);
+ info->attno = attr->attnum;
+ info->expr = build_generation_expression(relation, attr->attnum);
+
+ /*
+ * Run the expressions through eval_const_expressions. This is
+ * not just an optimization, but is necessary, because the
+ * planner will be comparing them to similarly-processed qual
+ * clauses, and may fail to detect valid matches without this.
+ * We must not use canonicalize_qual, however, since these
+ * aren't qual expressions.
+ */
+ info->expr = eval_const_expressions(NULL, info->expr);
+
+ /* May as well fix opfuncids too */
+ fix_opfuncids(info->expr);
+
+ /* Fix Vars to have the desired varno */
+ if (varno != 1)
+ ChangeVarNodes((Node *) info->expr, 1, varno, 0);
+
+ virtual_gencols = lappend(virtual_gencols, info);
+ }
+ }
+ }
+
+ return virtual_gencols;
+}
+
/*
* Try loading data for the statistics object.
*
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index af0b99243c6..21c86e8d21a 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -82,15 +82,6 @@ static void statext_store(Oid statOid, bool inh,
static int statext_compute_stattarget(int stattarget,
int nattrs, VacAttrStats **stats);
-/* Information needed to analyze a single simple expression. */
-typedef struct AnlExprData
-{
- Node *expr; /* expression to analyze */
- VacAttrStats *vacattrstat; /* statistics attrs to analyze */
-} AnlExprData;
-
-static void compute_expr_stats(Relation onerel, AnlExprData *exprdata,
- int nexprs, HeapTuple *rows, int numrows);
static Datum serialize_expr_stats(AnlExprData *exprdata, int nexprs);
static Datum expr_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
static AnlExprData *build_expr_data(List *exprs, int stattarget);
@@ -2083,7 +2074,7 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp,
/*
* Compute statistics about expressions of a relation.
*/
-static void
+void
compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
HeapTuple *rows, int numrows)
{
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 1c480cfaaf7..b651cb83416 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5408,6 +5408,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
*/
ListCell *ilist;
ListCell *slist;
+ ListCell *vlist;
/*
* The nullingrels bits within the expression could prevent us from
@@ -5527,6 +5528,46 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
break;
}
+ /*
+ * Search virtual generated columns for one with a matching expression
+ * and use the statistics collected for it if we have.
+ */
+ foreach(vlist, onerel->virtual_gencols)
+ {
+ VirtualGeneratedColumnInfo *info = (VirtualGeneratedColumnInfo *) lfirst(vlist);
+ Node *expr = info->expr;
+
+ /*
+ * Stop once we've found statistics for the expression (either
+ * for a virtual generated columns or an index in the preceding
+ * loop).
+ */
+ if (vardata->statsTuple)
+ break;
+
+ /* strip RelabelType before comparing it */
+ if (expr && IsA(expr, RelabelType))
+ expr = (Node *) ((RelabelType *) expr)->arg;
+
+ if (equal(node, expr))
+ {
+ Var *var = makeVar(onerel->relid,
+ info->attno,
+ vardata->atttype,
+ vardata->atttypmod,
+ exprCollation(node),
+ 0);
+ /*
+ * There cannot be a unique constraint on a virtual generated column.
+ * Other fields other than the stats tuple must be already set.
+ */
+ vardata->isunique = false;
+
+ /* Try to locate some stats */
+ examine_simple_variable(root, var, vardata);
+ }
+ }
+
/*
* Search extended statistics for one with a matching expression.
* There might be multiple ones, so just grab the first one. In the
@@ -5542,7 +5583,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
/*
* Stop once we've found statistics for the expression (either
- * from extended stats, or for an index in the preceding loop).
+ * from extended stats, or for an index or a virtual generated
+ * column in the preceding loop).
*/
if (vardata->statsTuple)
break;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 4a903d1ec18..4f0e0bf22e0 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -982,6 +982,8 @@ typedef struct RelOptInfo
List *indexlist;
/* list of StatisticExtInfo */
List *statlist;
+ /* list of VirtualGeneratedColumnInfo */
+ List *virtual_gencols;
/* size estimates derived from pg_class */
BlockNumber pages;
Cardinality tuples;
@@ -1355,6 +1357,23 @@ typedef struct StatisticExtInfo
List *exprs;
} StatisticExtInfo;
+/*
+ * VirtualGeneratedColumnInfo
+ * Information about virtual generated columns for planning/optimization
+ */
+typedef struct VirtualGeneratedColumnInfo
+{
+ pg_node_attr(no_copy_equal, no_read, no_query_jumble)
+
+ NodeTag type;
+
+ /* attribute number of virtual generated column */
+ AttrNumber attno;
+
+ /* generation expression */
+ Node *expr;
+} VirtualGeneratedColumnInfo;
+
/*
* JoinDomains
*
diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h
index efcb7dc3546..8eaea3b7566 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -68,6 +68,12 @@ typedef struct StatsBuildData
bool **nulls;
} StatsBuildData;
+/* Information needed to analyze a single simple expression. */
+typedef struct AnlExprData
+{
+ Node *expr; /* expression to analyze */
+ VacAttrStats *vacattrstat; /* statistics attrs to analyze */
+} AnlExprData;
extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data);
extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct);
@@ -127,4 +133,7 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root,
Selectivity *overlap_basesel,
Selectivity *totalsel);
+extern void
+compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
+ HeapTuple *rows, int numrows);
#endif /* EXTENDED_STATS_INTERNAL_H */
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..4469206dab0 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1493,6 +1493,13 @@ create table gtest32 (
);
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+ count
+-------
+ 5
+(1 row)
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..523ab188dac 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,6 +817,9 @@ create table gtest32 (
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
--
2.43.0
* [PATCH] Allow to collect statistics on virtual generated columns
@ 2025-07-18 00:58 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 7+ messages in thread
From: Yugo Nagata @ 2025-07-18 00:58 UTC (permalink / raw)
During ANALYZE, generation expressions are expanded, and statistics are
computed using compute_expr_stats(). To support this, both compute_expr_stats()
and AnlExprData are now exported from extended_stats.c.
To enable the optimizer to make use of these statistics, a new field named
virtual_gencols is added to RelOptInfo. This field holds the expressions of
virtual generated columns in the table. In examine_variable(), if an
expression in a WHERE clause matches a virtual generated column, the
corresponding statistics are used for that expression.
---
src/backend/commands/analyze.c | 32 ++++++----
src/backend/optimizer/util/plancat.c | 60 +++++++++++++++++++
src/backend/statistics/extended_stats.c | 11 +---
src/backend/utils/adt/selfuncs.c | 44 +++++++++++++-
src/include/nodes/pathnodes.h | 19 ++++++
.../statistics/extended_stats_internal.h | 9 +++
6 files changed, 154 insertions(+), 21 deletions(-)
inline patch 1: src/backend/commands/analyze.c, src/backend/optimizer/util/plancat.c, src/backend/statistics/extended_stats.c and 3 more (+154/-21 lines) download inline-1.patch
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 7111d5d5334..553f1bb52db 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -41,6 +41,7 @@
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -559,13 +560,28 @@ do_analyze_rel(Relation onerel, const VacuumParams params,
{
VacAttrStats *stats = vacattrstats[i];
AttributeOpts *aopt;
+ Form_pg_attribute attr = TupleDescAttr(onerel->rd_att, stats->tupattnum - 1);
- stats->rows = rows;
- stats->tupDesc = onerel->rd_att;
- stats->compute_stats(stats,
- std_fetch_func,
- numrows,
- totalrows);
+ /*
+ * For a virtual generated column, compute statistics for the expression value.
+ */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ AnlExprData *exprdata = (AnlExprData *) palloc0(sizeof(AnlExprData));
+
+ exprdata->expr = build_generation_expression(onerel, stats->tupattnum);
+ exprdata->vacattrstat = stats;
+ compute_expr_stats(onerel, exprdata, 1, rows, numrows);
+ }
+ else
+ {
+ stats->rows = rows;
+ stats->tupDesc = onerel->rd_att;
+ stats->compute_stats(stats,
+ std_fetch_func,
+ numrows,
+ totalrows);
+ }
/*
* If the appropriate flavor of the n_distinct option is
@@ -1049,10 +1065,6 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
if (attr->attisdropped)
return NULL;
- /* Don't analyze virtual generated columns */
- if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- return NULL;
-
/*
* Get attstattarget value. Set to -1 if null. (Analyze functions expect
* -1 to mean use default_statistics_target; see for example
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index c6a58afc5e5..68f7349ffc8 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -42,6 +42,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -77,6 +78,7 @@ static List *get_relation_constraints(PlannerInfo *root,
bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation);
static List *get_relation_statistics(RelOptInfo *rel, Relation relation);
static void set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
Relation relation);
@@ -508,6 +510,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->indexlist = indexinfos;
+ /* Make list of virtual generated columns */
+ rel->virtual_gencols = get_relation_virtual_gencols(rel, relation);
+
rel->statlist = get_relation_statistics(rel, relation);
/* Grab foreign-table info using the relcache, while we have it */
@@ -1482,6 +1487,61 @@ get_relation_constraints(PlannerInfo *root,
return result;
}
+/*
+ * get_relation_virtual_gencols
+ * Retrieve virtual generated columns defined on the table.
+ *
+ * Returns a List (possibly empty) of VirtualGeneratedColumnInfoInfo objects
+ * containing the generation expressions. Each one has been processed by
+ * eval_const_expressions(), and its Vars are changed to have the varno
+ * indicated by rel->relid. This allows the expressions to be easily
+ * compared to expressions taken from WHERE.
+ */
+static List *get_relation_virtual_gencols(RelOptInfo *rel, Relation relation)
+{
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ Index varno = rel->relid;
+ List *virtual_gencols = NIL;
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ VirtualGeneratedColumnInfo *info;
+
+ info = makeNode(VirtualGeneratedColumnInfo);
+ info->attno = attr->attnum;
+ info->expr = build_generation_expression(relation, attr->attnum);
+
+ /*
+ * Run the expressions through eval_const_expressions. This is
+ * not just an optimization, but is necessary, because the
+ * planner will be comparing them to similarly-processed qual
+ * clauses, and may fail to detect valid matches without this.
+ * We must not use canonicalize_qual, however, since these
+ * aren't qual expressions.
+ */
+ info->expr = eval_const_expressions(NULL, info->expr);
+
+ /* May as well fix opfuncids too */
+ fix_opfuncids(info->expr);
+
+ /* Fix Vars to have the desired varno */
+ if (varno != 1)
+ ChangeVarNodes((Node *) info->expr, 1, varno, 0);
+
+ virtual_gencols = lappend(virtual_gencols, info);
+ }
+ }
+ }
+
+ return virtual_gencols;
+}
+
/*
* Try loading data for the statistics object.
*
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index a8b63ec0884..e26352cacec 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -82,15 +82,6 @@ static void statext_store(Oid statOid, bool inh,
static int statext_compute_stattarget(int stattarget,
int nattrs, VacAttrStats **stats);
-/* Information needed to analyze a single simple expression. */
-typedef struct AnlExprData
-{
- Node *expr; /* expression to analyze */
- VacAttrStats *vacattrstat; /* statistics attrs to analyze */
-} AnlExprData;
-
-static void compute_expr_stats(Relation onerel, AnlExprData *exprdata,
- int nexprs, HeapTuple *rows, int numrows);
static Datum serialize_expr_stats(AnlExprData *exprdata, int nexprs);
static Datum expr_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
static AnlExprData *build_expr_data(List *exprs, int stattarget);
@@ -2103,7 +2094,7 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp,
/*
* Compute statistics about expressions of a relation.
*/
-static void
+void
compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
HeapTuple *rows, int numrows)
{
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 17fbfa9b410..00e3972ff4f 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5408,6 +5408,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
*/
ListCell *ilist;
ListCell *slist;
+ ListCell *vlist;
Oid userid;
/*
@@ -5576,6 +5577,46 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
break;
}
+ /*
+ * Search virtual generated columns for one with a matching expression
+ * and use the statistics collected for it if we have.
+ */
+ foreach(vlist, onerel->virtual_gencols)
+ {
+ VirtualGeneratedColumnInfo *info = (VirtualGeneratedColumnInfo *) lfirst(vlist);
+ Node *expr = info->expr;
+
+ /*
+ * Stop once we've found statistics for the expression (either
+ * for a virtual generated columns or an index in the preceding
+ * loop).
+ */
+ if (vardata->statsTuple)
+ break;
+
+ /* strip RelabelType before comparing it */
+ if (expr && IsA(expr, RelabelType))
+ expr = (Node *) ((RelabelType *) expr)->arg;
+
+ if (equal(node, expr))
+ {
+ Var *var = makeVar(onerel->relid,
+ info->attno,
+ vardata->atttype,
+ vardata->atttypmod,
+ exprCollation(node),
+ 0);
+ /*
+ * There cannot be a unique constraint on a virtual generated column.
+ * Other fields other than the stats tuple must be already set.
+ */
+ vardata->isunique = false;
+
+ /* Try to locate some stats */
+ examine_simple_variable(root, var, vardata);
+ }
+ }
+
/*
* Search extended statistics for one with a matching expression.
* There might be multiple ones, so just grab the first one. In the
@@ -5591,7 +5632,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
/*
* Stop once we've found statistics for the expression (either
- * from extended stats, or for an index in the preceding loop).
+ * from extended stats, or for an index or a virtual generated
+ * column in the preceding loop).
*/
if (vardata->statsTuple)
break;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index e5dd15098f6..0c173f307b7 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -970,6 +970,8 @@ typedef struct RelOptInfo
List *indexlist;
/* list of StatisticExtInfo */
List *statlist;
+ /* list of VirtualGeneratedColumnInfo */
+ List *virtual_gencols;
/* size estimates derived from pg_class */
BlockNumber pages;
Cardinality tuples;
@@ -1322,6 +1324,23 @@ typedef struct StatisticExtInfo
List *exprs;
} StatisticExtInfo;
+/*
+ * VirtualGeneratedColumnInfo
+ * Information about virtual generated columns for planning/optimization
+ */
+typedef struct VirtualGeneratedColumnInfo
+{
+ pg_node_attr(no_copy_equal, no_read, no_query_jumble)
+
+ NodeTag type;
+
+ /* attribute number of virtual generated column */
+ AttrNumber attno;
+
+ /* generation expression */
+ Node *expr;
+} VirtualGeneratedColumnInfo;
+
/*
* JoinDomains
*
diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h
index efcb7dc3546..8eaea3b7566 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -68,6 +68,12 @@ typedef struct StatsBuildData
bool **nulls;
} StatsBuildData;
+/* Information needed to analyze a single simple expression. */
+typedef struct AnlExprData
+{
+ Node *expr; /* expression to analyze */
+ VacAttrStats *vacattrstat; /* statistics attrs to analyze */
+} AnlExprData;
extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data);
extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct);
@@ -127,4 +133,7 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root,
Selectivity *overlap_basesel,
Selectivity *totalsel);
+extern void
+compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
+ HeapTuple *rows, int numrows);
#endif /* EXTENDED_STATS_INTERNAL_H */
--
2.43.0
* [PATCH v4] Allow to collect statistics on virtual generated columns
@ 2025-07-18 00:58 Yugo Nagata <nagata@sraoss.co.jp>
0 siblings, 0 replies; 7+ messages in thread
From: Yugo Nagata @ 2025-07-18 00:58 UTC (permalink / raw)
During ANALYZE, generation expressions are expanded, and statistics are
computed using compute_expr_stats(). To support this, both compute_expr_stats()
and AnlExprData are now exported from extended_stats.c.
To enable the optimizer to make use of these statistics, a new field named
virtual_gencols is added to RelOptInfo. This field holds the expressions of
virtual generated columns in the table. In examine_variable(), if an
expression in a WHERE clause matches a virtual generated column, the
corresponding statistics are used for that expression.
---
doc/src/sgml/ref/alter_table.sgml | 9 +--
src/backend/commands/analyze.c | 32 +++++++---
src/backend/optimizer/util/plancat.c | 62 +++++++++++++++++++
src/backend/statistics/extended_stats.c | 11 +---
src/backend/utils/adt/selfuncs.c | 44 ++++++++++++-
src/include/nodes/pathnodes.h | 19 ++++++
.../statistics/extended_stats_internal.h | 9 +++
.../regress/expected/generated_virtual.out | 7 +++
src/test/regress/sql/generated_virtual.sql | 3 +
9 files changed, 168 insertions(+), 28 deletions(-)
inline patch 1: doc/src/sgml/ref/alter_table.sgml, src/backend/commands/analyze.c, src/backend/optimizer/util/plancat.c and 6 more (+168/-28 lines) download inline-1.patch
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8867da6c693..bffb07775f7 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -210,8 +210,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
When this form is used, the column's statistics are removed,
so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
@@ -275,12 +273,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- When this form is used on a stored generated column, its statistics
- are removed, so running
- <link linkend="sql-analyze"><command>ANALYZE</command></link>
+ When this form is used, the column's statistics are removed,
+ so running <link linkend="sql-analyze"><command>ANALYZE</command></link>
on the table afterwards is recommended.
- For a virtual generated column, <command>ANALYZE</command>
- is not necessary because such columns never have statistics.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 8ea2913d906..c53e0f0eacf 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -40,6 +40,7 @@
#include "parser/parse_oper.h"
#include "parser/parse_relation.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "statistics/extended_stats_internal.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -558,13 +559,28 @@ do_analyze_rel(Relation onerel, const VacuumParams params,
{
VacAttrStats *stats = vacattrstats[i];
AttributeOpts *aopt;
+ Form_pg_attribute attr = TupleDescAttr(onerel->rd_att, stats->tupattnum - 1);
- stats->rows = rows;
- stats->tupDesc = onerel->rd_att;
- stats->compute_stats(stats,
- std_fetch_func,
- numrows,
- totalrows);
+ /*
+ * For a virtual generated column, compute statistics for the expression value.
+ */
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ AnlExprData *exprdata = (AnlExprData *) palloc0(sizeof(AnlExprData));
+
+ exprdata->expr = build_generation_expression(onerel, stats->tupattnum);
+ exprdata->vacattrstat = stats;
+ compute_expr_stats(onerel, exprdata, 1, rows, numrows);
+ }
+ else
+ {
+ stats->rows = rows;
+ stats->tupDesc = onerel->rd_att;
+ stats->compute_stats(stats,
+ std_fetch_func,
+ numrows,
+ totalrows);
+ }
/*
* If the appropriate flavor of the n_distinct option is
@@ -1048,10 +1064,6 @@ examine_attribute(Relation onerel, int attnum, Node *index_expr)
if (attr->attisdropped)
return NULL;
- /* Don't analyze virtual generated columns */
- if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
- return NULL;
-
/*
* Get attstattarget value. Set to -1 if null. (Analyze functions expect
* -1 to mean use default_statistics_target; see for example
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 4536bdd6cb4..c2ee90c9d35 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -42,6 +42,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "partitioning/partdesc.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "statistics/statistics.h"
#include "storage/bufmgr.h"
@@ -77,6 +78,8 @@ static List *get_relation_constraints(PlannerInfo *root,
bool include_partition);
static List *build_index_tlist(PlannerInfo *root, IndexOptInfo *index,
Relation heapRelation);
+static List *get_relation_virtual_gencols(PlannerInfo *root, RelOptInfo *rel,
+ Relation relation);
static List *get_relation_statistics(PlannerInfo *root, RelOptInfo *rel,
Relation relation);
static void set_relation_partition_info(PlannerInfo *root, RelOptInfo *rel,
@@ -509,6 +512,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->indexlist = indexinfos;
+ /* Make list of virtual generated columns */
+ rel->virtual_gencols = get_relation_virtual_gencols(root, rel, relation);
+
rel->statlist = get_relation_statistics(root, rel, relation);
/* Grab foreign-table info using the relcache, while we have it */
@@ -1487,6 +1493,62 @@ get_relation_constraints(PlannerInfo *root,
return result;
}
+/*
+ * get_relation_virtual_gencols
+ * Retrieve virtual generated columns defined on the table.
+ *
+ * Returns a List (possibly empty) of VirtualGeneratedColumnInfoInfo objects
+ * containing the generation expressions. Each one has been processed by
+ * eval_const_expressions(), and its Vars are changed to have the varno
+ * indicated by rel->relid. This allows the expressions to be easily
+ * compared to expressions taken from WHERE.
+ */
+static List *get_relation_virtual_gencols(PlannerInfo *root, RelOptInfo *rel,
+ Relation relation)
+{
+ TupleDesc tupdesc = RelationGetDescr(relation);
+ Index varno = rel->relid;
+ List *virtual_gencols = NIL;
+
+ if (tupdesc->constr && tupdesc->constr->has_generated_virtual)
+ {
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ VirtualGeneratedColumnInfo *info;
+
+ info = makeNode(VirtualGeneratedColumnInfo);
+ info->attno = attr->attnum;
+ info->expr = build_generation_expression(relation, attr->attnum);
+
+ /*
+ * Run the expressions through eval_const_expressions. This is
+ * not just an optimization, but is necessary, because the
+ * planner will be comparing them to similarly-processed qual
+ * clauses, and may fail to detect valid matches without this.
+ * We must not use canonicalize_qual, however, since these
+ * aren't qual expressions.
+ */
+ info->expr = eval_const_expressions(NULL, info->expr);
+
+ /* May as well fix opfuncids too */
+ fix_opfuncids(info->expr);
+
+ /* Fix Vars to have the desired varno */
+ if (varno != 1)
+ ChangeVarNodes((Node *) info->expr, 1, varno, 0);
+
+ virtual_gencols = lappend(virtual_gencols, info);
+ }
+ }
+ }
+
+ return virtual_gencols;
+}
+
/*
* Try loading data for the statistics object.
*
diff --git a/src/backend/statistics/extended_stats.c b/src/backend/statistics/extended_stats.c
index af0b99243c6..21c86e8d21a 100644
--- a/src/backend/statistics/extended_stats.c
+++ b/src/backend/statistics/extended_stats.c
@@ -82,15 +82,6 @@ static void statext_store(Oid statOid, bool inh,
static int statext_compute_stattarget(int stattarget,
int nattrs, VacAttrStats **stats);
-/* Information needed to analyze a single simple expression. */
-typedef struct AnlExprData
-{
- Node *expr; /* expression to analyze */
- VacAttrStats *vacattrstat; /* statistics attrs to analyze */
-} AnlExprData;
-
-static void compute_expr_stats(Relation onerel, AnlExprData *exprdata,
- int nexprs, HeapTuple *rows, int numrows);
static Datum serialize_expr_stats(AnlExprData *exprdata, int nexprs);
static Datum expr_fetch_func(VacAttrStatsP stats, int rownum, bool *isNull);
static AnlExprData *build_expr_data(List *exprs, int stattarget);
@@ -2083,7 +2074,7 @@ examine_opclause_args(List *args, Node **exprp, Const **cstp,
/*
* Compute statistics about expressions of a relation.
*/
-static void
+void
compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
HeapTuple *rows, int numrows)
{
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 1c480cfaaf7..b651cb83416 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -5408,6 +5408,7 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
*/
ListCell *ilist;
ListCell *slist;
+ ListCell *vlist;
/*
* The nullingrels bits within the expression could prevent us from
@@ -5527,6 +5528,46 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
break;
}
+ /*
+ * Search virtual generated columns for one with a matching expression
+ * and use the statistics collected for it if we have.
+ */
+ foreach(vlist, onerel->virtual_gencols)
+ {
+ VirtualGeneratedColumnInfo *info = (VirtualGeneratedColumnInfo *) lfirst(vlist);
+ Node *expr = info->expr;
+
+ /*
+ * Stop once we've found statistics for the expression (either
+ * for a virtual generated columns or an index in the preceding
+ * loop).
+ */
+ if (vardata->statsTuple)
+ break;
+
+ /* strip RelabelType before comparing it */
+ if (expr && IsA(expr, RelabelType))
+ expr = (Node *) ((RelabelType *) expr)->arg;
+
+ if (equal(node, expr))
+ {
+ Var *var = makeVar(onerel->relid,
+ info->attno,
+ vardata->atttype,
+ vardata->atttypmod,
+ exprCollation(node),
+ 0);
+ /*
+ * There cannot be a unique constraint on a virtual generated column.
+ * Other fields other than the stats tuple must be already set.
+ */
+ vardata->isunique = false;
+
+ /* Try to locate some stats */
+ examine_simple_variable(root, var, vardata);
+ }
+ }
+
/*
* Search extended statistics for one with a matching expression.
* There might be multiple ones, so just grab the first one. In the
@@ -5542,7 +5583,8 @@ examine_variable(PlannerInfo *root, Node *node, int varRelid,
/*
* Stop once we've found statistics for the expression (either
- * from extended stats, or for an index in the preceding loop).
+ * from extended stats, or for an index or a virtual generated
+ * column in the preceding loop).
*/
if (vardata->statsTuple)
break;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 4a903d1ec18..4f0e0bf22e0 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -982,6 +982,8 @@ typedef struct RelOptInfo
List *indexlist;
/* list of StatisticExtInfo */
List *statlist;
+ /* list of VirtualGeneratedColumnInfo */
+ List *virtual_gencols;
/* size estimates derived from pg_class */
BlockNumber pages;
Cardinality tuples;
@@ -1355,6 +1357,23 @@ typedef struct StatisticExtInfo
List *exprs;
} StatisticExtInfo;
+/*
+ * VirtualGeneratedColumnInfo
+ * Information about virtual generated columns for planning/optimization
+ */
+typedef struct VirtualGeneratedColumnInfo
+{
+ pg_node_attr(no_copy_equal, no_read, no_query_jumble)
+
+ NodeTag type;
+
+ /* attribute number of virtual generated column */
+ AttrNumber attno;
+
+ /* generation expression */
+ Node *expr;
+} VirtualGeneratedColumnInfo;
+
/*
* JoinDomains
*
diff --git a/src/include/statistics/extended_stats_internal.h b/src/include/statistics/extended_stats_internal.h
index efcb7dc3546..8eaea3b7566 100644
--- a/src/include/statistics/extended_stats_internal.h
+++ b/src/include/statistics/extended_stats_internal.h
@@ -68,6 +68,12 @@ typedef struct StatsBuildData
bool **nulls;
} StatsBuildData;
+/* Information needed to analyze a single simple expression. */
+typedef struct AnlExprData
+{
+ Node *expr; /* expression to analyze */
+ VacAttrStats *vacattrstat; /* statistics attrs to analyze */
+} AnlExprData;
extern MVNDistinct *statext_ndistinct_build(double totalrows, StatsBuildData *data);
extern bytea *statext_ndistinct_serialize(MVNDistinct *ndistinct);
@@ -127,4 +133,7 @@ extern Selectivity mcv_clause_selectivity_or(PlannerInfo *root,
Selectivity *overlap_basesel,
Selectivity *totalsel);
+extern void
+compute_expr_stats(Relation onerel, AnlExprData *exprdata, int nexprs,
+ HeapTuple *rows, int numrows);
#endif /* EXTENDED_STATS_INTERNAL_H */
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index aca6347babe..4469206dab0 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1493,6 +1493,13 @@ create table gtest32 (
);
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+ count
+-------
+ 5
+(1 row)
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index ba19bc4c701..523ab188dac 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -817,6 +817,9 @@ create table gtest32 (
insert into gtest32 values (1), (2);
analyze gtest32;
+-- Ensure that statistics on virtual generated column are available
+select count(*) from pg_stats where tablename = 'gtest32';
+
-- Ensure that nullingrel bits are propagated into the generation expressions
explain (costs off)
select sum(t2.b) over (partition by t2.a),
--
2.43.0
* Modernizing pg_bsd_indent's error/warning reporting code
@ 2026-06-12 16:59 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2026-06-12 16:59 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Buildfarm member greenfly has recently started to warn about
some very hoary code in pg_bsd_indent [1]:
../pgsql/src/tools/pg_bsd_indent/io.c:562:27: warning: diagnostic behavior may be improved by adding the 'format(printf, 2, 3)' attribute to the declaration of 'diag4' [-Wmissing-format-attribute]
562 | fprintf(stdout, msg, a, b);
| ^
../pgsql/src/tools/pg_bsd_indent/io.c:579:24: warning: diagnostic behavior may be improved by adding the 'format(printf, 2, 3)' attribute to the declaration of 'diag3' [-Wmissing-format-attribute]
579 | fprintf(stdout, msg, a);
| ^
This is not an unreasonable suggestion, and presumably more people
will start seeing this as they adopt newer clang versions. (I see
the same on Fedora 44, for instance.) So I think we ought to take
the advice, and while we're at it let's convert this code to use
varargs instead of several duplicative functions. Patch attached.
regards, tom lane
[1] https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=greenfly&dt=2026-06-11%2018%3A59%3...
Attachments:
v1-0001-Modernize-pg_bsd_indent-s-error-warning-reporting.patch (text/x-diff, 4.3K) download
From 2c4f7a3c84f17ea9d855758fb2d0eeae1d2c4a3e Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 12 Jun 2026 12:46:10 -0400
Subject: [PATCH v1] Modernize pg_bsd_indent's error/warning reporting code.
Late-model clang complains that these functions should be labeled
with "format(printf, 2, 3)", and it's right. But let's go a bit
further and also make use of varargs, to remove duplication and
allow these functions to be used with non-integer input values.
Since no good deed goes unpunished, I had to also adjust a couple
of call sites. They weren't wrong as-is, since the size_t-sized
arguments were coerced to int on the way into diag3(). But
without that, we have to adjust the format strings.
---
src/tools/pg_bsd_indent/indent.c | 4 +--
src/tools/pg_bsd_indent/indent.h | 9 ++++---
src/tools/pg_bsd_indent/io.c | 43 +++++---------------------------
3 files changed, 14 insertions(+), 42 deletions(-)
diff --git a/src/tools/pg_bsd_indent/indent.c b/src/tools/pg_bsd_indent/indent.c
index 1a29409173b..101554ba5df 100644
--- a/src/tools/pg_bsd_indent/indent.c
+++ b/src/tools/pg_bsd_indent/indent.c
@@ -536,7 +536,7 @@ check_type:
case lparen: /* got a '(' or '[' */
/* count parens to make Healy happy */
if (++ps.p_l_follow == nitems(ps.paren_indents)) {
- diag3(0, "Reached internal limit of %d unclosed parens",
+ diag3(0, "Reached internal limit of %zd unclosed parens",
nitems(ps.paren_indents));
ps.p_l_follow--;
}
@@ -809,7 +809,7 @@ check_type:
* declaration or an init */
di_stack[ps.dec_nest] = dec_ind;
if (++ps.dec_nest == nitems(di_stack)) {
- diag3(0, "Reached internal limit of %d struct levels",
+ diag3(0, "Reached internal limit of %zd struct levels",
nitems(di_stack));
ps.dec_nest--;
}
diff --git a/src/tools/pg_bsd_indent/indent.h b/src/tools/pg_bsd_indent/indent.h
index e9e71d667d8..974ffe1ac27 100644
--- a/src/tools/pg_bsd_indent/indent.h
+++ b/src/tools/pg_bsd_indent/indent.h
@@ -39,9 +39,7 @@ int compute_label_target(void);
int count_spaces(int, char *);
int count_spaces_until(int, char *, char *);
int lexi(struct parser_state *);
-void diag2(int, const char *);
-void diag3(int, const char *, int);
-void diag4(int, const char *, int, int);
+void diag(int level, const char *msg, ...) pg_attribute_printf(2, 3);
void dump_line(void);
int lookahead(void);
void lookahead_reset(void);
@@ -51,3 +49,8 @@ void pr_comment(void);
void set_defaults(void);
void set_option(char *);
void set_profile(const char *);
+
+/* backwards-compatibility macros */
+#define diag2(level, msg) diag(level, msg)
+#define diag3(level, msg, a) diag(level, msg, a)
+#define diag4(level, msg, a, b) diag(level, msg, a, b)
diff --git a/src/tools/pg_bsd_indent/io.c b/src/tools/pg_bsd_indent/io.c
index 62d600bbb11..787b3ce6177 100644
--- a/src/tools/pg_bsd_indent/io.c
+++ b/src/tools/pg_bsd_indent/io.c
@@ -553,53 +553,22 @@ count_spaces(int cur, char *buffer)
}
void
-diag4(int level, const char *msg, int a, int b)
+diag(int level, const char *msg, ...)
{
- if (level)
- found_err = 1;
- if (output == stdout) {
- fprintf(stdout, "/**INDENT** %s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stdout, msg, a, b);
- fprintf(stdout, " */\n");
- }
- else {
- fprintf(stderr, "%s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stderr, msg, a, b);
- fprintf(stderr, "\n");
- }
-}
-
-void
-diag3(int level, const char *msg, int a)
-{
- if (level)
- found_err = 1;
- if (output == stdout) {
- fprintf(stdout, "/**INDENT** %s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stdout, msg, a);
- fprintf(stdout, " */\n");
- }
- else {
- fprintf(stderr, "%s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stderr, msg, a);
- fprintf(stderr, "\n");
- }
-}
+ va_list ap;
-void
-diag2(int level, const char *msg)
-{
+ va_start(ap, msg);
if (level)
found_err = 1;
if (output == stdout) {
fprintf(stdout, "/**INDENT** %s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stdout, "%s", msg);
+ vfprintf(stdout, msg, ap);
fprintf(stdout, " */\n");
}
else {
fprintf(stderr, "%s@%d: ", level == 0 ? "Warning" : "Error", line_no);
- fprintf(stderr, "%s", msg);
+ vfprintf(stderr, msg, ap);
fprintf(stderr, "\n");
}
+ va_end(ap);
}
-
--
2.52.0
* Re: Modernizing pg_bsd_indent's error/warning reporting code
@ 2026-06-12 20:05 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Ayush Tiwari @ 2026-06-12 20:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@lists.postgresql.org
Hi,
On Fri, 12 Jun 2026 at 22:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Buildfarm member greenfly has recently started to warn about
> some very hoary code in pg_bsd_indent [1]:
>
> ../pgsql/src/tools/pg_bsd_indent/io.c:562:27: warning: diagnostic behavior
> may be improved by adding the 'format(printf, 2, 3)' attribute to the
> declaration of 'diag4' [-Wmissing-format-attribute]
> 562 | fprintf(stdout, msg, a, b);
> | ^
> ../pgsql/src/tools/pg_bsd_indent/io.c:579:24: warning: diagnostic behavior
> may be improved by adding the 'format(printf, 2, 3)' attribute to the
> declaration of 'diag3' [-Wmissing-format-attribute]
> 579 | fprintf(stdout, msg, a);
> | ^
>
> This is not an unreasonable suggestion, and presumably more people
> will start seeing this as they adopt newer clang versions. (I see
> the same on Fedora 44, for instance.) So I think we ought to take
> the advice, and while we're at it let's convert this code to use
> varargs instead of several duplicative functions. Patch attached.
>
Patch looks good to me,
Just a minor comment, while we are at it, should we change
the %zd to %zu for nitems since it looks like it uses size_t?
Regards,
Ayush
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Modernizing pg_bsd_indent's error/warning reporting code
@ 2026-06-15 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2026-06-15 16:26 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
> On Fri, 12 Jun 2026 at 22:29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This is not an unreasonable suggestion, and presumably more people
>> will start seeing this as they adopt newer clang versions. (I see
>> the same on Fedora 44, for instance.) So I think we ought to take
>> the advice, and while we're at it let's convert this code to use
>> varargs instead of several duplicative functions. Patch attached.
> Patch looks good to me,
Thanks for looking!
> Just a minor comment, while we are at it, should we change
> the %zd to %zu for nitems since it looks like it uses size_t?
Seems reasonable enough. It can't matter functionally, since
the denominators in nitems() are surely at least 2. But it is
indeed customary to print size_t with %zu, so done that way.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-06-15 16:26 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-18 00:58 [PATCH v2] Allow to collect statistics on virtual generated columns Yugo Nagata <nagata@sraoss.co.jp>
2025-07-18 00:58 [PATCH v3] Allow to collect statistics on virtual generated columns Yugo Nagata <nagata@sraoss.co.jp>
2025-07-18 00:58 [PATCH] Allow to collect statistics on virtual generated columns Yugo Nagata <nagata@sraoss.co.jp>
2025-07-18 00:58 [PATCH v4] Allow to collect statistics on virtual generated columns Yugo Nagata <nagata@sraoss.co.jp>
2026-06-12 16:59 Modernizing pg_bsd_indent's error/warning reporting code Tom Lane <tgl@sss.pgh.pa.us>
2026-06-12 20:05 ` Re: Modernizing pg_bsd_indent's error/warning reporting code Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-06-15 16:26 ` Re: Modernizing pg_bsd_indent's error/warning reporting code Tom Lane <tgl@sss.pgh.pa.us>
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