Date: Tue, 2 Sep 2025 16:33:41 +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: <20250902163341.c174d0e83eedfc54d68b8e9c@sraoss.co.jp>
In-Reply-To: <20250820141028.dc06e0d1d787ce1151f69ed6@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>
	<20250820141028.dc06e0d1d787ce1151f69ed6@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Tue__2_Sep_2025_16_33_41_+0900_LULRV8Lnk+KDzwmW"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?O9SZYRsg5Hpiadm8AQ45l1tYEgPPXlKdAs9cbw3FhHPriidFAeI4I9fedLmk?=
 =?us-ascii?Q?G5CPiGmb64BzwQW6UCaVoxqLSLndLXWGDU0PJqylwfRmXTs84L4Q7Cx8XnEb?=
 =?us-ascii?Q?MHIvjDNcqj/f+IQCjRfjICL4YW2Hlv1LYCvYOQN0MAbsJSvEobBsmFZm5HHJ?=
 =?us-ascii?Q?H9k6w1UK3w59GT85QziUcU3/Rk7AbWqvppPRlnM5M87t6J8GZYE/wkS8WLhD?=
 =?us-ascii?Q?SyhQNpWTpU55WL3OMHLvgDm8N8aRUkKbV2EcLn/laFIG5Zjall4rES/gbUB3?=
 =?us-ascii?Q?HAupEMQ2Wa4+Y+q0vhkLDk40kjw2MuECYS9sdG2FciSNiMElM5JTitEIDd7C?=
 =?us-ascii?Q?mWxq9TY8tUibfcaTefINW2h/UpE1tXy5PxNEC+/473o0fVVOkipLpQ9R6Jtb?=
 =?us-ascii?Q?pACArN6pZtRziu+Ap6DCowqf3oDIdwCM6oHCdqdz6DuUCJ6CFSwyuLd9RGux?=
 =?us-ascii?Q?PPageXt54r4US2oy4UIMkfGVa28aBHkP9zQnrMWGVsEW6c4I+kXN2chW8f1E?=
 =?us-ascii?Q?VApkkNeh7Z7sR3asW2T5YxoAAfpJ/5it28pEhZ0Jx6XVQN9zbOcv+sC03oYB?=
 =?us-ascii?Q?Cb6FeVsqWKROnf1UuEcf3W6TEWe25RFiVC3x/ruPKN+C5DawyqIDTjK06YhO?=
 =?us-ascii?Q?xR5kHFPhmZm7BVAXdFNvENKZ3NxFuLxUUMQ0kpXTFGqnt0u0k0C4j6PHg1cF?=
 =?us-ascii?Q?+qnZASYmxH/C7JZaYYOEhO1cofqIt58ydVqwIsS6WRDXmjDpLd3n7BP6Q/En?=
 =?us-ascii?Q?Nw3oQF1RGF11OUqHzutsy9cWW1sFv+rNgIbnyxznhMGlu0nZsqrPZD4QenAw?=
 =?us-ascii?Q?M+fkST2azNB89yBUx1FHDpmDhjK6vpKZ63OBDviOdKbObJWe0aWI6cOdas4q?=
 =?us-ascii?Q?51I5wVXzpudqfdHsyxalnwxJubvo14ZfVW9yU3fnM7mRbsfuPnFgMUEE7QQ8?=
 =?us-ascii?Q?a//5DfhRO/UVk5OlPgHagiqA9LeTle02EWEg96+oNXMkIOpio8S4Gc16gE97?=
 =?us-ascii?Q?WE3pFeTeNFVSPk7A/ervyeTf4TbP44JSfosu23J4KlaHPfO9P/ec/782/Bi6?=
 =?us-ascii?Q?t0Z4Lz9SQSr77yuB6t6wwIuxx0NuCg9yZ8RnH+KTVmmJYVXOd1HUeiIJ///P?=
 =?us-ascii?Q?M3+1hdeOfc+4ecKClnGpBT1IVMl+fEKG0l2lG0d/S6nZJUVde7CjDJE58oJC?=
 =?us-ascii?Q?1U0ar/yrM9PA0w/xm9bTbpWGBjkgXdo7zOgA3Xva84PEk3VW2F9nBNM96LAr?=
 =?us-ascii?Q?ZaQIesHPDRsElIRzGUjk+0U6ILZKFVaONnGUIQ6dbuI+RQsqvnN3NO3m5fO5?=
 =?us-ascii?Q?ivhGEn/VDx49/8dJPFUsnCTY0iy+o3Z//qldFpITgbDdF8qpFVsr9XJ/8T8P?=
 =?us-ascii?Q?7/MI8tDmC4F8nRIXBnHkJFUrr1t2snIZdDEZo0mY9pT5OWSb/7wgnaHl5jBx?=
 =?us-ascii?Q?rMQJsos8bDCPNEst3cpXvyi8KT/99n2W7cjO7ek17ccSqQyU0ATShQDANHY6?=
 =?us-ascii?Q?38+MHunGEbQb/980htUBK21Gf5il+CKxhMUEi3v9GRYtkm5lgthK6ExtDlTw?=
 =?us-ascii?Q?TGziQjpQ3G1XiOEvC9G5ZDCtJmIy67Ym55SJLM0h?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 2fd99443-da49-44ff-4d5b-08dde9f30a93
X-MS-Exchange-CrossTenant-AuthSource: OSZSPRMB0015.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Sep 2025 07:33:42.2330
 (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: 
 8ccZ1Sae7MIN5mx3O4Ym7m1Ur/5l6uuYFUy/ol89uhhyo+XbrOaNyLqOBDQVRYlorktyN7o2Mgdazrq1HAZ7rA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY4P286MB6228
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/20250902163341.c174d0e83eedfc54d68b8e9c%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Tue__2_Sep_2025_16_33_41_+0900_LULRV8Lnk+KDzwmW
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Wed, 20 Aug 2025 14:10:28 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

> 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=_Tue__2_Sep_2025_16_33_41_+0900_LULRV8Lnk+KDzwmW
Content-Type: text/x-diff;
 name="v4-0001-Allow-to-collect-statistics-on-virtual-generated-.patch"
Content-Disposition: attachment;
 filename="v4-0001-Allow-to-collect-statistics-on-virtual-generated-.patch"
Content-Transfer-Encoding: 7bit