Date: Wed, 31 Dec 2025 19:44:55 +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: <20251231194455.5975fda68ed8e941e4afb805@sraoss.co.jp>
In-Reply-To: <20250902163341.c174d0e83eedfc54d68b8e9c@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>
	<20250902163341.c174d0e83eedfc54d68b8e9c@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Wed__31_Dec_2025_19_44_55_+0900_Ok0z3oC/bht_7Z=e"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?8KbX/Gj/cGtKs8f4uN7yR+CHUiYkK1GwzwjGXi8/AEgtT7FFdhPHji7AWGUh?=
 =?us-ascii?Q?zoCJgu9VwQysM9xAph7XF+yJn0ENv2VohGUWBL9BaKFkpK5K0RO0yDHFByJ0?=
 =?us-ascii?Q?CcxiYQOt1v4m8zb3TPd6BWnvCzdRJhBXecP/EBRrHB9YCf23b/+J2wvvrmI8?=
 =?us-ascii?Q?k3x816LdLUR4bPWZBsBEbjsJ4nfoN9R7SiImhlfolvPgwFsFIJhjkuVmw1Qx?=
 =?us-ascii?Q?DlhwTWSON8eEwe9/1++6nNKrQBi7kKuyGcLqcJU4jshqvPHj0el12gAz0VoP?=
 =?us-ascii?Q?jkWJwmYMUZgFZEkEw871iCI/dStruncfB1LMPZHKKDWQzTAETjECq3FGi3V9?=
 =?us-ascii?Q?st2lhSueJDtC1GKBvZyblg+qVViGIomCba/X5JwrhIopemuQcMp2rObLvFq1?=
 =?us-ascii?Q?nn59r7scnQ/j7rq6SaTJZnkJwKtGA4Kv6Ey0GNBVBka3i1g8i0q+5K2qFbo3?=
 =?us-ascii?Q?wybjWP8Tuflp+6dW8+Xa64X7yuMqcm4lxN7oPjcD49Q45QvOetvClCP1AlnA?=
 =?us-ascii?Q?9ViKulEUcCFQ9XMbIdE65NnMhULbInB/fCPyTYV3vJeW96dA8ZuYYMD6fKsM?=
 =?us-ascii?Q?IKeFn8KAiCuq8Yf7kDiw3tlZhEEobo2FR+jBwILY1PtcxDpLw/ug7mbkwUPd?=
 =?us-ascii?Q?g/eNSpGYmvno9D2vVw/LUh8LndF9fh7mSkn10ezaQS5Dh9O+i3t+bqZpl015?=
 =?us-ascii?Q?k1iI/yo1IoKYt7oeL+8OwdoLnGtJFx66//SQqDctC0t7LnHhCBGHnLcwOrYg?=
 =?us-ascii?Q?h2DPutBKnFLFYyDUNu/a3VDi1/oJDYTE/8r6udbfxM2oEG+FuLk//+IgI4Nu?=
 =?us-ascii?Q?MRJ2cMdeJYsTqsVfUTGN8ZKgJA74fAmFKukcvjmazBwm13fG0/kMlsto4vF8?=
 =?us-ascii?Q?xl1fXcsfGfaJpZRTMgV09hWCDHXIHW4h7bwVk57BWb+nL6sMSUrjSX3hybhS?=
 =?us-ascii?Q?Nhd0W4yxorS8P8s3OEmHevXw1woBuYYDvJJ4n8vabZQoPlifuAJuFkgvmtvC?=
 =?us-ascii?Q?ygK3qwJ+YLaVEHEOa4I3+47Kv3RsNJRMX4wzCzk32qefnVMpGvByapGTOvPX?=
 =?us-ascii?Q?vuEnvRp+exBYthQTaCsGk/2WBO8Z36vy5YOOu+JTYqZYhu4cEMG/5ANFeStR?=
 =?us-ascii?Q?0Gnz/Y6Gl4rdrtTKpv56lg4IouZ3humnbEg2idlV/o8sFJLeMJoJJL9U1fzt?=
 =?us-ascii?Q?8LmrgCWE/WOHZW8+QVOWxTUVU4vb0/T9Gk60rc/pBtzbApch6Y+uHaKNXbLG?=
 =?us-ascii?Q?w5yitg7PwGu7mon3c7sloBwxr6zkwf5gzpHVSQi3fRkEEX0Q1zT+Iud1nyra?=
 =?us-ascii?Q?K6OVP8VKjsJCeYrp7mS7J/kkUdk77nby5Q46r4NfyH6FKu+CfqA2uFgi8rl5?=
 =?us-ascii?Q?Ngqs+bw/p8khdagmjjaFF4irPXSFe++MgW7jCEUaxAArJ5+1vpp9iAKzUezp?=
 =?us-ascii?Q?knjdUTekX7u6f+VeImJ53mP19vILhj771JAcq3mUD0qsx8ZUYPsP8dounp8Q?=
 =?us-ascii?Q?vs4kEWR9F30tahCc737Tpw813gpuPqjoJeXYuDlkEEVILJm/4jgBtPb8RGBo?=
 =?us-ascii?Q?7iCKID8sCJ8A/0uuZMvl/PPLp7Ki5Pk26Z/XTGNaqSUTe98rdEdEAbLW80g8?=
 =?us-ascii?Q?5QvCftapT9Yon/AQmt0yh8avc9CeHI/nO8bVFe8QCIHFXs7uxpu0mvOJBFBK?=
 =?us-ascii?Q?G/hti9eLSum6DaP/ck9qh3mJ11LRiH9sRirL9KbgHutP2xv04KB/U4atetZr?=
 =?us-ascii?Q?HXkAlGTHtQ=3D=3D?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 bba4786a-7597-4a38-c178-08de4859a386
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 31 Dec 2025 10:44:56.7120
 (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: 
 k1zB9nWXi8iTn3OR6y9+Vk0DeXZC0ynIVWO4nhrS0rXahyvIKNZ+YmbOhmQuHd4PsuhIse42xZ3+t390AYe3XQ==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY4P286MB6407
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/20251231194455.5975fda68ed8e941e4afb805%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Wed__31_Dec_2025_19_44_55_+0900_Ok0z3oC/bht_7Z=e
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Tue, 2 Sep 2025 16:33:41 +0900
Yugo Nagata <nagata@sraoss.co.jp> wrote:

> > > > 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 an updated patch that fixes the broken test since 10c4fe074a.

Regards,
Yugo Nagata

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

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