Date: Wed, 20 Aug 2025 14:10:28 +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: <20250820141028.dc06e0d1d787ce1151f69ed6@sraoss.co.jp>
In-Reply-To: <20250808122125.e9eaff938f8c83556f337e50@sraoss.co.jp>
References: <20250422181006.dd6f9d1d81299f5b2ad55e1a@sraoss.co.jp>
	<kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
	<20250624170533.3caeec7d6034cd5ddf4f00d2@sraoss.co.jp>
	<20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp>
	<20250808122125.e9eaff938f8c83556f337e50@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Wed__20_Aug_2025_14_10_28_+0900_u9fdIbTgN.58HH3l"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?5Bxt8LKiQXv2pGkpnZNx9wHLJIFM9NRmoaXLxrYreSM+25UPrBKXPgVdeZzD?=
 =?us-ascii?Q?FB4dmNCF9/P6iN1+BnEZdL+85ihtUjKfLPc+7WWLDMF6mLrmHd2hq48ApPcl?=
 =?us-ascii?Q?cDYDXBwy166gPJ/gI/dvCiHkf4BnYOSiPcPX4kCpJn0gc8m8u7lu0jIInZE+?=
 =?us-ascii?Q?5ntZfinE5A/Yo3EDdAbpmNAIRdu3mDk0zSfdOuiEFAE+Y9smhtcXacjAr3IV?=
 =?us-ascii?Q?3XWagz1dXUwlVwwnoAMZ2G/RHHg/kAK+CdNTZ1HGUsE6geGo0LqlRrZyrOQP?=
 =?us-ascii?Q?vl5Nm3SXVdyd12enk+ZDrgE7CvBkAlq5cBP2DcV1OZHdIDoOf/Cwug5+TsL8?=
 =?us-ascii?Q?Sj3U6HGQGavWkNflqA6SbWQU57lj6VvXkbs4Y39D1/YF6l2dnaStsUiaLcdQ?=
 =?us-ascii?Q?VS2XP/G/2rL6fnJ5H7rWxoLCVQBPfxNTMZO2zJHZoTP4XmYpYE36ilp070XE?=
 =?us-ascii?Q?t2JLyUoybDhVpMy+gcKoR2l4fO/aLWDyymafEaLhE+coLxKpT25fQ2M382yX?=
 =?us-ascii?Q?XeSQZe3ziYMGIwi1si8CVLs8YJ3vTwQhlIQyT2zUF14opfigmnNpjzJu0m6P?=
 =?us-ascii?Q?RNhZzAdaAnz5HkOmwReYwPSikbbbXPujG3+4cB6zjYqYW9J85G2EnJZntFJZ?=
 =?us-ascii?Q?QkHUMpDFzjl7owmUsOYf3g67zQLYIctlQe7wp6rGMnALjAdqQjAAtO/0m2TO?=
 =?us-ascii?Q?M7sECFIsZzjI3t2TDF8Xdc9u4KmDJcr6v/h6qgp+i+K2zsm9jmxnwMY6iFM6?=
 =?us-ascii?Q?42wI8vBtn3zkVIeE/g144jY9vvyy4bW0e2eCzBAMNtCoBgSvRWKvU6hwXY8X?=
 =?us-ascii?Q?rV10EBgsRHfO5i6UdCyeiZcSnoeKYjJV9PuwjY0IxL81vhQ/1c6g7K3MA51Q?=
 =?us-ascii?Q?VO+HWFxt/x18amrQO5MYZ3wiosZiP1kkJRlm5YtCsfZljY0tYVlveDTDGNXL?=
 =?us-ascii?Q?L3PJcL9YSzxcdBuhjtzKUKdnouphiCc9DleON/WxLG/4eu1+lpepLJIP1gL0?=
 =?us-ascii?Q?bLJuNL5BIdh0GrJA1/tVzcTQqev61Q/BsOZsJzbhI/P0pEiKaV19PXTYQpW8?=
 =?us-ascii?Q?85lDdS9S7/8V1PkQIMSuLBSFpYKw/pXttsBGp1AM6GOKJKZ0ybJiG71mROSK?=
 =?us-ascii?Q?65XMA5z9UKvOo6kLJZB4X0JzornGieGLb/UjOFQNZJF9nuwtSpSzJSAsJjaB?=
 =?us-ascii?Q?8pn1ALGUkOucuVL7FJfYAQf/MImKY5bc8cHwa3J6AK5tLQo8w4dIMbeD498N?=
 =?us-ascii?Q?Dt/Mx3ULOYB9biVXV25MREmrtFoEzUWjnQGq2POdtD/Br4sUT8U2N3L6A7aH?=
 =?us-ascii?Q?mXbhBp/2j0yB7ERXTLx46kqRqWiWfMfAyIgS2gJZLdxwaD1FXAX4+gAkVGkt?=
 =?us-ascii?Q?ugjaKRywbOLJkJvNSY8bLdBUgnLsyTb9TdY3A6g8wVO9KafNAVrPrhJa7B+Q?=
 =?us-ascii?Q?Wnu/a8Hxv3xnIyKCjuEjMZzWBQAttxDF3biuTuJhp++6qgeMrOd1F3Mm1ljN?=
 =?us-ascii?Q?1laIgSzcUxN4vGBr8deL8rkVRRl9C8a1gU3vEbDmPrBnRH75skKpNMEhHXU6?=
 =?us-ascii?Q?cWRh0Tf1KluWpmcbXQT3KT4esIHyuyki3wY+Pp/B?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 4ab588b9-44bb-4e7f-abd9-08dddfa7e15d
X-MS-Exchange-CrossTenant-AuthSource: TYCP286MB2162.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 20 Aug 2025 05:10:29.1480
 (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: 
 cXXsr33pHhGhsNC6hSDerg1SZCrHeyzK/336aC6nkgf6Rpxit3kWUpMRC98kQqCMMJXDokAdLego+tU/3GSung==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYXP286MB6089
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/20250820141028.dc06e0d1d787ce1151f69ed6%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Wed__20_Aug_2025_14_10_28_+0900_u9fdIbTgN.58HH3l
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Fri, 8 Aug 2025 12:21:25 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

> 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.

I've attached a rebased patch.

Regards,
Yugo Nagata

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

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