Date: Fri, 8 Aug 2025 12:21:25 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Yugo Nagata <nagata@sraoss.co.jp>
Cc: Andres Freund <andres@anarazel.de>, <pgsql-hackers@postgresql.org>
Subject: Re: Allow to collect statistics on virtual generated columns
Message-Id: <20250808122125.e9eaff938f8c83556f337e50@sraoss.co.jp>
In-Reply-To: <20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp>
References: <20250422181006.dd6f9d1d81299f5b2ad55e1a@sraoss.co.jp>
	<kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
	<20250624170533.3caeec7d6034cd5ddf4f00d2@sraoss.co.jp>
	<20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Fri__8_Aug_2025_12_21_25_+0900_yNJX4hBoaUaCAKCp"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?IIedV/X2Oj+VooPtwV+ewZmjtsvRTTMbOiAVq4AzAdVV4Pj00vqqRUjLj3Mq?=
 =?us-ascii?Q?wrMj7sD20OIO36JSVTRvkQolXiDDGkCOxqt02JmrROKwH+klAodyXGnbC3Vt?=
 =?us-ascii?Q?XZF5itL7mJhQD/9JTtR3d48prJUsS4SxCFCEeZtgfCwtCZV87FAxYhR5yif/?=
 =?us-ascii?Q?i0n/oUc07Gap40/vj0jLBlMG3/fkQUOj+oJ1i20NUwj2Mw+2oQjaTZqZ+wuD?=
 =?us-ascii?Q?PRD5BMoKIecpxUh1uIn1R7/rzOlFQ8u6H67ln7OGeTIuLRSM2apkALyiDtWL?=
 =?us-ascii?Q?8vZAwmdZcc6UNUYmehg17ihmm/GuYu+D2WAu2lsOgAOiWb5DzAY/fsZo+GOd?=
 =?us-ascii?Q?BSCJrtyn3xsDbatZHc2AOqbrVwxnJ0pSdOw3Dg/g8g3YOCOnWmXGNu5HYcXt?=
 =?us-ascii?Q?JECMHrzyiIwcA72g9Ca+X5CRPWWOoa/uS9HWW6V/Sgs89B4llgHUddGqjlsf?=
 =?us-ascii?Q?XCBfkzrhXM4BnOSwu2O4Nd32OwGJNmq/WXzQbghCdRRicH3MCairXuArqM3F?=
 =?us-ascii?Q?yhKpactqczeQzqMZgkjppAw7OjEiZ+2VQ2ZbykfPHCe1B6SF1SNcl+mXxhyf?=
 =?us-ascii?Q?XYdBNX6hbDQvQqx3OC7wGQq6Ky40bgyB9Y7FIG2o3wFWbfhF0NG5LnxF8985?=
 =?us-ascii?Q?WiUQv/Uwih3iwjX+ehr9X87GjnylS9j4BYbY2qy+qEJ19BZlC362zgJDIeg9?=
 =?us-ascii?Q?GOJ7PQX4A1XDkzq64QjBZI2brW8zedNBfP0WbKAUSpnnoqkrhdm69iCwcBAj?=
 =?us-ascii?Q?Xy9FWV7w4mnnw11kpW9E40UIbTnJHGTpwgGzvfrnVXlAa2Yi/w50olXWDIdx?=
 =?us-ascii?Q?mDiuvK73VlyNT4H78dbo07Jh6TqaqIGRyvKPraC+juntoKdlIBhipkZX9xnG?=
 =?us-ascii?Q?wPQ9t3/6gP9ueOnmlqm53Cc19LFAj6uOk8+isTH2w6kIzgcQblb+OcqQR77H?=
 =?us-ascii?Q?di50tVcIgW5iR0e6JczomaU30WuHd0MGDAjSfE+oIT8V3wd5fRUzKn+NFUxB?=
 =?us-ascii?Q?DvwJPbu7tDryMwv43GK1VUPTqW26gpPckLoM59KwZyO5rJQQw2qZ7z5wqCL0?=
 =?us-ascii?Q?CRDSQfOpOMjIZMw9crRDhnXJUZsI0FsG2K9JD5631HARyMC5/5LaQ6JlIiOH?=
 =?us-ascii?Q?1GEDH/eo2azCvdQv8L2cAZlO74mfWTIHhoQ/hwrcNpQVesXyxdxxAlXnbqnR?=
 =?us-ascii?Q?kSe2XWTnoKSvPytmXfRlc2JHrpO7bNxVvRSV3+i+EayjjsVBuDiwCEeWia0Z?=
 =?us-ascii?Q?KqdyKvkrRx70m7syhE3CuYc9pf2Es11m90u4eoLQ3X1+xY6CCPKGUeQcKa9z?=
 =?us-ascii?Q?4hnb4WxvgPVEm3rWcskMz3PBicvOu7ACHU+tbf96eaxP6awn7cQDTDkc/dk6?=
 =?us-ascii?Q?wO46BgEV/HD2Sje1XFF1NVZ9HuCsYRLJwdtaWQnWfhaMxB6IgUd/ruYSC/Il?=
 =?us-ascii?Q?sTYqnaITBccgpgIraey6j4eC1PtSmH/9A2Vdy2IvW877Kpkk2cHDSW/icgv+?=
 =?us-ascii?Q?AfkiDrfp+eTULmiplk6qpDkjx2nUlNNgLSdZPznXxQKkmpF/FPthjfFEy1G+?=
 =?us-ascii?Q?pYQ9whTbw/CNTD3sRK5n3Xoic7gvxztruszeVJW7IGsDl5sdZ6dxXtpnk4fu?=
 =?us-ascii?Q?IQ/fU0gpYJ+1lCYb31ySM+jibbHObUiHnX4LpXj3930w?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 6f3e51ec-4220-4585-0707-08ddd62aa85c
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 08 Aug 2025 03:21:25.9403
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: 
 Fjbc0nEwbcRHBqXvlMDHuSEbWyDDK6/7GurgMJjIL3G2dCxQr2nb2r2UySDdwc4a7Zk94TFX3jCLArzoYw/PLA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS7P286MB5555
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20250808122125.e9eaff938f8c83556f337e50%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Fri__8_Aug_2025_12_21_25_+0900_yNJX4hBoaUaCAKCp
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Fri, 1 Aug 2025 00:28:30 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

> Hi,
> 
> On Tue, 24 Jun 2025 17:05:33 +0900
> Yugo Nagata <nagata@sraoss.co.jp> wrote:
> 
> > Instead, I'm thinking of an alternative approach: expanding the expression
> > at the time statistics are collected.
> 
> I've attached a new patch in this approache.
> 
> This allows to collect statistics on virtual generated columns.
> 
> 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. However, since they are no longer specific
> to extended statistics, it might be better to move them to analyze.c and vacuum.h.
> 
> 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.
> 
> Example:
> 
> - Before applying the patch, the cardinality estimate is erroneous.
> 
> test=# create table t (i int, j int generated always as (i*10) virtual);
> CREATE TABLE
> test=# insert into t select generate_series(1,1000);
> INSERT 0 1000
> test=# insert into t select 1 from generate_series(1,1000);
> INSERT 0 1000
> test=# analyze t;
> ANALYZE
> test=# explain analyze select * from t where j = 10;
>                                            QUERY PLAN                                            
> -------------------------------------------------------------------------------------------------
>  Seq Scan on t  (cost=0.00..0.02 rows=1 width=8) (actual time=0.031..0.806 rows=1001.00 loops=1)
>    Filter: ((i * 10) = 10)
>    Rows Removed by Filter: 999
>    Buffers: shared hit=9
>  Planning:
>    Buffers: shared hit=10
>  Planning Time: 0.299 ms
>  Execution Time: 0.948 ms
> (8 rows)
> 
> 
> - After applying the patch, the cardinality estimate is correct.
> 
> test=# analyze t;
> ANALYZE
> test=# explain analyze select * from t where j = 10;
>                                              QUERY PLAN                                              
> -----------------------------------------------------------------------------------------------------
>  Seq Scan on t  (cost=0.00..41.50 rows=1001 width=8) (actual time=0.034..0.871 rows=1001.00 loops=1)
>    Filter: ((i * 10) = 10)
>    Rows Removed by Filter: 999
>    Buffers: shared hit=9
>  Planning:
>    Buffers: shared hit=6
>  Planning Time: 0.374 ms
>  Execution Time: 1.028 ms
> (8 rows)
> 
> 
> Note that the patch is still a work in progress, so documentation and tests are not included.

I've attached an updated patch.

I modified the documentation to remove the statement that virtual generated columns
do not have statistics.

In addition, I added a test to ensure that statistics on virtual generated columns
are available.

Regards,
Yugo Nagata

-- 
Yugo Nagata <nagata@sraoss.co.jp>

--Multipart=_Fri__8_Aug_2025_12_21_25_+0900_yNJX4hBoaUaCAKCp
Content-Type: text/x-diff;
 name="v2-0001-Allow-to-collect-statistics-on-virtual-generated-.patch"
Content-Disposition: attachment;
 filename="v2-0001-Allow-to-collect-statistics-on-virtual-generated-.patch"
Content-Transfer-Encoding: 7bit