Date: Fri, 1 Aug 2025 00:28:30 +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: <20250801002830.143b25971fb9594b89d96aee@sraoss.co.jp>
In-Reply-To: <20250624170533.3caeec7d6034cd5ddf4f00d2@sraoss.co.jp>
References: <20250422181006.dd6f9d1d81299f5b2ad55e1a@sraoss.co.jp>
	<kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
	<20250624170533.3caeec7d6034cd5ddf4f00d2@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Fri__1_Aug_2025_00_28_30_+0900_PkRV9PSGNhLRtsZF"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?YtOT++zUsVoEgnMWJ7xv8lYBYw6B4DgmOv/YeN53WT0BCJfEMMmjPacNGiva?=
 =?us-ascii?Q?cQdGz4yfP+zIgZ+HUFPJn6pXIO9UpCsMitUJlVNi/v+VoBOc0FkelrklquHl?=
 =?us-ascii?Q?ZInpLSJAwSueCRpmJca3YLF5+OHexsqIwmyHIAmLGEwG1q9RiitPiCZGfOsz?=
 =?us-ascii?Q?ELHQVXN4LMFWqnshpLJXh4XFSEs2yGmpMAD/6Tl3P6JpU0ucifUX1WzkqZtj?=
 =?us-ascii?Q?LVsu5AGNxG89S1OpNTVu8iqvp7ytkDumEOu8WWNa6J5cUBSSN4nirNULdx16?=
 =?us-ascii?Q?nUoXy7QAOvh0Cpx/kd6cdUqWLZ3vjWMo64QWaxltXG4ozIsSem3QVzDXP/ww?=
 =?us-ascii?Q?PCMQNhz5y1tTn1DyXGAW4g172uRFDWD92CpTKV6R++hmUPA/hZZnF2DaROVq?=
 =?us-ascii?Q?Zfd1v00tCsOMxkdQOtAch7rROKPLicJdj9EytDf74h4g+f0a8zdvPKHZ5gga?=
 =?us-ascii?Q?f+KPd/z5SIN1b38q4Ekrcy7ZJ6GEyQU+JwK0AwmPzYRPIHWDY8vX7epNZTHb?=
 =?us-ascii?Q?qeCWVnJKTcRcHy+lsIYeTMOYjGNODaJmPDkinWX2u0m+BHX/rEadWrNQQpH/?=
 =?us-ascii?Q?paL/J2lsypX4uaDFjyLBdxt8CHk4EVHs1ac5YwcXqpFeSO605R+eaC8x2PjH?=
 =?us-ascii?Q?I6eIjFjwfk6dKpRJ7Cjs9Ew23aXXPlZ6/yUW8Cpfn5EX/aY15LK0Cpd07/Kg?=
 =?us-ascii?Q?a0ac+Yt0mr7JbfEEcUSnfLdzbiqSjyk3yL9+QNAWesxAwFKNsL7GhYqGtYUk?=
 =?us-ascii?Q?l8rQOn9NghNncYdUD1bL4PUpgmnQ/DzswKwvaBO+Tc3jXzkyNRydXpsRc2Zb?=
 =?us-ascii?Q?1MKm52JLr55iLJBMUmBmHN7iyv2kIhnP3Zpxw1qm/tZ7XnuSCogpP12rVP7t?=
 =?us-ascii?Q?xSA64JXJk0r5l79DNSCCoit09GytQiK5lqncUWJkLSxSH/XvtI6fFbNZ7xwT?=
 =?us-ascii?Q?Q6ThzDlTWh1clMDz4/mpnfpUtOrPIsnR8R26u1RKVUc2vc8LggOLJ9phNWHZ?=
 =?us-ascii?Q?Jny+GZPbdw396Fhv0WXdAlS2caczHNqci8se/VXYkWddYX2B02hlymc9wm7n?=
 =?us-ascii?Q?XlaFskpL/VmG8xzCcBctyL8beSb4AieWAMMpEhG/HAIaFvhRG6yt4Tv6waDG?=
 =?us-ascii?Q?Dc781mgFSe+M7BLcRRiPqnEnhwyrtQGLGsfe33JM0tOAkWZxna8PjEtQby5D?=
 =?us-ascii?Q?zznJzwu2mNy4lnGTeE4Fn05IsEPhOcPftZPRab2uhae47zqoKwBR0MYZjyWd?=
 =?us-ascii?Q?GwLft9EZn3NIWPl2NzlcPGsltfXxiS8sVXAyQTUNQanGZAYvK36+OTp8T5f8?=
 =?us-ascii?Q?STMsR4tS6953sjp9YNaF22x1a+KRVXe9lJ6WeJpfnV+tadJT2Xy7mp94VwaC?=
 =?us-ascii?Q?FpL1iUWlFB4xPkaUOYlvlv3xxTrcin5nw3kCPPSsygFKS723sSPi4KTQIaQl?=
 =?us-ascii?Q?iQMBaWnLmPmFTA/4PBotLl/RXUr6FFij8V9fyDAhmzjiOILmZzEH/kFrWo26?=
 =?us-ascii?Q?8HuAUHTFC2GJBwW9a0l9Caeenmwr/44YEQyiBhK0Phb68ioYNSiMGSXSPXu2?=
 =?us-ascii?Q?mulZ5ux75Dssr4WDMjkl/s0JRfF3htu7ZR79u/BXdy+l3aVMYbHjkcH+aQEm?=
 =?us-ascii?Q?FkwB0/WNOID2H6eBuJ3Ki3VR1Xk04fNrsNDmxu6KRjxI?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 d94ef002-5c0e-4b93-4c5d-08ddd046e78b
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 31 Jul 2025 15:28:30.9515
 (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: 
 Aw597O0Fup6STpRgkvCg/fz1T/RJNbvXxotCbt8uurtPJ8tQhAAJ8cgXr6ibYFRQOJw+jzWtIFBMFWerf/KvuA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYYP286MB4873
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/20250801002830.143b25971fb9594b89d96aee%40sraoss.co.jp>
Precedence: bulk

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

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.

Regards,
Yugo Nagata


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

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