Date: Tue, 24 Jun 2025 17:05:33 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Allow to collect statistics on virtual generated columns
Message-Id: <20250624170533.3caeec7d6034cd5ddf4f00d2@sraoss.co.jp>
In-Reply-To: <kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
References: <20250422181006.dd6f9d1d81299f5b2ad55e1a@sraoss.co.jp>
	<kp43uuykq64fyqxnlihq2rzntarextvzibuzturajfucl6bayo@6fcnflboemtt>
Content-Type: multipart/mixed;
 boundary="Multipart=_Tue__24_Jun_2025_17_05_33_+0900_4Q+AqXBpJicclgog"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?dxC8/oGKEonaI674tsbto4s2L/asETs7cC49HNSl91ut59Vu/b0DoIOdmQ7K?=
 =?us-ascii?Q?dt95ZgAZL4KVulMYN4uWzxKQ1GiYpVu6YkKc4p3Jqkodn2WrzAqRE2z3uLVZ?=
 =?us-ascii?Q?3fSi73hwP/8Kgj29A/EXpk/J7HJgOmGP78objMjTcicZEE8kBT6akchsVn1C?=
 =?us-ascii?Q?tsznfgQMetT5J/PX7NGRpnZClYS4BZ+r0wbZyxFD0Wk8/RpoNZoFbNM0uiJ/?=
 =?us-ascii?Q?EPbiqbaZ2TlD6W0F3x1vMsIXMg00mKS+ZjdTfwt5pTYzpxEY/iPc8LDvISuP?=
 =?us-ascii?Q?XwCcNLICUiPu8sVA3JNHE319uVCYbRmaUDJdRHVw9Fs/n6XZ2GPjzX7SAdyA?=
 =?us-ascii?Q?lujK+9X8z40lhyPGm725oBgIGRW/QUBhwPt7JK8T+fH6s1WzBUNzbnlPsFfL?=
 =?us-ascii?Q?ck1o7dG6c7lPNi2+f/WBsCNS+jHdumn2c7jsjQ6c6rOlGtexfKZxmn8cY9JV?=
 =?us-ascii?Q?BqMabcQE9ZHA4jq7QHkU93hwfDfrNBPDUREq7VeygfBoIQKfV0FDx8GfD/S7?=
 =?us-ascii?Q?qiQAyKgQ5p+kwR7wt5r4b2BAj0rr1zYpfYlqOLUqJdsawtj/z7kyG7D1emJW?=
 =?us-ascii?Q?Q8a6iddO59E+AsH0U1kkJFM0PLreW6sxgrEgzHkFAZBXbQTu3RakvPdb46bS?=
 =?us-ascii?Q?P6LAj4D/LjWEa42/txTFWMUimfkpNDW6cg0lIcz7X75fqKTz/2RSfuxOxQwh?=
 =?us-ascii?Q?hmcEYV/q9SyICSHS/nT9+plPogf/qadNxv21mMyoKIP57vTGdYlc7MT4f10k?=
 =?us-ascii?Q?RgjPhHrkm0P4dzCUCuIy/Hruwz6pTfxH4TtfTvIizvKOJ0CVIV4r123MK3ZT?=
 =?us-ascii?Q?Zg4gnPSiP4N0DXghOjfa2oxbS41q/oBblrcj1JoscrPfTFdVAmjUgvft6MWG?=
 =?us-ascii?Q?JcyJYkDj3pr9tTvLQUBrm/nntawmobMQSUI4G1YP3zfDMJdfqGE0/9z3kdg5?=
 =?us-ascii?Q?YyurK7cVH3Bf7faCWHCIKWNDyzA+xDq9tbYdSF7qGBfmHB/S/V+GKQ2OeqI2?=
 =?us-ascii?Q?P8iMqMXK2wlFu8daemdwb6aWvWGXSZYh0BIc4dCowjPP5mLmRvAI+r2FNqkh?=
 =?us-ascii?Q?tqsOJCut3CplbsfyROaaveLUpbLC6lBIRh0St7TOjnYALdWGCx3QGFsAg21o?=
 =?us-ascii?Q?xMS1TTm95TenXJTR7T1lg9vjvNbLyzztHeBmOVhWQ9r9R9J5h9WLIQGEko29?=
 =?us-ascii?Q?+X5GqyNzX19Ub31+qJ797HnlJwH4TX5ty2OoxMkob/HX9xJYvG9HLHKblbTR?=
 =?us-ascii?Q?CDOkHE0CR/aJOSM9RGmTAI/yn2W6tkGdivClo9li5Cnc6hetn+4F3lKCOwGM?=
 =?us-ascii?Q?jTY8bQcwPmbqF+101d4Jys/1xjc0oE/5J0ZNiP9sDicvMefP/Q2g6YRrHJuR?=
 =?us-ascii?Q?vDLlgZDI259pdddUmEeK222q3e7d++9viTUJ8ySVdCvJHHtXSM4sykJy8T0l?=
 =?us-ascii?Q?QXuzI2jlCwjLPcjL0117QkAlrKZReLrNQlYIagQNmQzFFMwjYRirm6MV/cpb?=
 =?us-ascii?Q?XbHXNMEeTzifb1jiZzaynb0CmXj5zeXVStdMc7PLAOiUeIg2l9+9X7r3F9Ui?=
 =?us-ascii?Q?P66+PkPjrJIs7xz0LuyrkzeuxA1uW+pbPuvSO+7jhZJ+xz3yUE2r9TRh388H?=
 =?us-ascii?Q?uzjQJKfurbkHqHgLXQabMmIIh+BE0uBwqPq5Q0ZozFsT?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 6dbd8e68-6c77-4081-c948-08ddb2f5e55e
X-MS-Exchange-CrossTenant-AuthSource: OSZP286MB2160.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 24 Jun 2025 08:05:34.3503
 (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: 
 9cJtl1o06n9ND31GHh0vY7DB4cCsgUimZglBPyOGfWLJlXLGVZRqF8PA6IeaKjL0i6mgW5MCZseen8tvuStNKA==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: OS3P286MB2491
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/20250624170533.3caeec7d6034cd5ddf4f00d2%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Tue__24_Jun_2025_17_05_33_+0900_4Q+AqXBpJicclgog
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Tue, 17 Jun 2025 10:43:41 -0400
Andres Freund <andres@anarazel.de> wrote:

> Hi,
> 
> On 2025-04-22 18:10:06 +0900, Yugo Nagata wrote:
> > With your feedback, I would like to progress or rework the patch.
> 
> Right now the tests seem to always fail:
> https://cirrus-ci.com/github/postgresql-cfbot/postgresql/cf%2F571

Thank you for letting me know it.

I've attached an updated patch to fix the test failure.

However, I'm now reconsidering the current approach, where the expression
of a virtual generated column is expanded at the time of creating extended
statistics. This seems not be ideal, as the statistics would become useless
if the expression is later modified.

Instead, I'm thinking of an alternative approach: expanding the expression
at the time statistics are collected.

Best regards,
Yugo Nagata

> 
> Fails e.g. with:
> https://api.cirrus-ci.com/v1/artifact/task/5921189782093824/testrun/build/testrun/regress/regress/regression.diffs
> 
> diff -U3 /tmp/cirrus-ci-build/src/test/regress/expected/stats_ext.out /tmp/cirrus-ci-build/build/testrun/regress/regress/results/stats_ext.out
> --- /tmp/cirrus-ci-build/src/test/regress/expected/stats_ext.out	2025-05-26 00:59:01.813042000 +0000
> +++ /tmp/cirrus-ci-build/build/testrun/regress/regress/results/stats_ext.out	2025-05-26 01:02:20.350387000 +0000
> @@ -56,7 +56,6 @@
>  ERROR:  unrecognized statistics kind "unrecognized"
>  -- incorrect expressions
>  CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
> -ERROR:  extended statistics require at least 2 columns
>  CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses
>  ERROR:  syntax error at or near "+"
>  LINE 1: CREATE STATISTICS tst ON y + z FROM ext_stats_test;
> @@ -69,25 +68,24 @@
>  -- statistics on virtual generated column not allowed
>  CREATE TABLE ext_stats_test1 (x int, y int, z int GENERATED ALWAYS AS (x+y) VIRTUAL, w xid);
>  CREATE STATISTICS tst on z from ext_stats_test1;
> -ERROR:  statistics creation on virtual generated columns is not supported
>  CREATE STATISTICS tst on (z) from ext_stats_test1;
> -ERROR:  statistics creation on virtual generated columns is not supported
> +ERROR:  statistics object "tst" already exists
>  CREATE STATISTICS tst on (z+1) from ext_stats_test1;
> -ERROR:  statistics creation on virtual generated columns is not supported
> +ERROR:  statistics object "tst" already exists
>  CREATE STATISTICS tst (ndistinct) ON z from ext_stats_test1;
> -ERROR:  statistics creation on virtual generated columns is not supported
> +ERROR:  statistics object "tst" already exists
>  -- statistics on system column not allowed
>  CREATE STATISTICS tst on tableoid from ext_stats_test1;
> -ERROR:  statistics creation on system columns is not supported
> +ERROR:  statistics object "tst" already exists
>  CREATE STATISTICS tst on (tableoid) from ext_stats_test1;
> -ERROR:  statistics creation on system columns is not supported
> +ERROR:  statistics object "tst" already exists
>  CREATE STATISTICS tst on (tableoid::int+1) from ext_stats_test1;
> -ERROR:  statistics creation on system columns is not supported
> +ERROR:  statistics object "tst" already exists
>  CREATE STATISTICS tst (ndistinct) ON xmin from ext_stats_test1;
> -ERROR:  statistics creation on system columns is not supported
> +ERROR:  statistics object "tst" already exists
>  -- statistics without a less-than operator not supported
>  CREATE STATISTICS tst (ndistinct) ON w from ext_stats_test1;
> -ERROR:  column "w" cannot be used in statistics because its type xid has no default btree operator class
> +ERROR:  statistics object "tst" already exists
>  DROP TABLE ext_stats_test1;
>  -- Ensure stats are dropped sanely, and test IF NOT EXISTS while at it
>  CREATE TABLE ab1 (a INTEGER, b INTEGER, c INTEGER);
> 
> Greetings,
> 
> Andres
> 
> 


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

--Multipart=_Tue__24_Jun_2025_17_05_33_+0900_4Q+AqXBpJicclgog
Content-Type: text/x-diff;
 name="v2-0001-Allow-to-create-extended-statistics-on-virtual-ge.patch"
Content-Disposition: attachment;
 filename="v2-0001-Allow-to-create-extended-statistics-on-virtual-ge.patch"
Content-Transfer-Encoding: 7bit