Re: examine_variable ignored CollateExpr

Original
>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>Date: 2026-08-16 23:59
>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Cc: jian he <jian(dot)universality(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
>Subject: Re: examine_variable ignored CollateExpr
>
>"=?utf-8?B?Wml6aHVhbkxpdSBYLU1BTg==?=" <44973863(at)qq(dot)com> writes:
>> My current proposal is along the following lines:
>
>> * eval_const_expressions() should already have stripped adjacent
>> * RelabelTypes. However, stripping PlaceHolderVars above may have
>> * brought previously separated RelabelTypes into adjacency, whether
>> * at the top level or within a deeper subtree. Therefore, run
>> * eval_const_expressions() again to normalize the expression.
>
>That is a ridiculously expensive way to fix this problem.
>
>regards, tom lane

Hi, Tom
Thank you for the reminder and guidance.

>> * eval_const_expressions() again to normalize the expression.
>
>That is a ridiculously expensive way to fix this problem.

I understand the concern that calling eval_const_expressions() again can be relatively expensive.
I took another look at this function and also reviewed the related functions around strip_all_phvs_deep().

(To Richard Guo<rguo(at)postgresql(dot)org>, I’ve specifically CC’d you because you implemented the functions
related to `strip_all_phvs_deep()`.
https://www.postgresql.org/message-id/flat/E1va3GH-003FBe-35%40gemulon.postgresql.org
)

One reason why eval_const_expressions() can be expensive is that it invokes eval_const_expressions_mutator(),
which may create new nodes with makeNode() or copy existing nodes using copyObject()/memcpy().
As a result, each additional invocation of eval_const_expressions() may introduce considerable memory allocation
and copying overhead.

Based on the related functions around strip_all_phvs_deep() and applyRelabelType(), I implemented an in-place
function specifically for this purpose. It avoids the unnecessary allocation and copying of expression trees while
performing the required normalization, and should therefore be significantly cheaper than calling eval_const_expressions() again.

The following are the test scenarios and SQL I used previously:
(As mentioned earlier, these tests only cover specific scenarios and are not comprehensive.
We may need to add more performance tests as well.)

drop COLLATION if exists case_insensitive;
CREATE COLLATION if not exists case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

drop table if exists my_table;
CREATE TABLE my_table (name text COLLATE "C", address text COLLATE "case_insensitive");

INSERT INTO my_table SELECT chr(65 + g % 52), chr(65 + g % 52) FROM generate_series(1, 500) g;
--INSERT INTO my_table SELECT 'a','a' FROM generate_series(1, 2) g;
--INSERT INTO my_table SELECT 'A','A' FROM generate_series(1, 2) g;

ANALYZE my_table;

--View the regular statistics
select * from pg_catalog.pg_stats where tablename = 'my_table'\gx

drop index if exists idx_my_table_address_5;
create index idx_my_table_address_5 on my_table(lower(address COLLATE "case_insensitive" COLLATE "C"));

--View the index information. Note that indexprs is stored as a raw parse tree.
select c.relname,i.* from pg_catalog.pg_index i, pg_catalog.pg_class c where i.indexrelid = c.oid and c.relname like 'idx_my_table_%'\gx

analyze my_table;

--v0/v3 both use idx_my_table_address_5
explain analyze select lower(address COLLATE "case_insensitive" COLLATE "C"), count(*)
from my_table group by lower(address COLLATE "case_insensitive" COLLATE "C");

DROP STATISTICS if exists stx_my_table_name_2;
CREATE STATISTICS stx_my_table_name_2 ON (name COLLATE "case_insensitive" COLLATE "C" COLLATE "case_insensitive") FROM my_table;
analyze my_table;

-- View the definition of the extended statistics.Note that stxexprs is stored as a raw parse tree.
select * from pg_catalog.pg_statistic_ext where stxname = 'stx_my_table_name_2'\gx

-- v0 does not use the extended statistics stx_my_table_name_2,
-- but instead uses the regular statistics for column name.
-- v3 uses the extended statistics stx_my_table_name_2.
explain analyze select name COLLATE "case_insensitive", count(*)
from my_table group by name COLLATE "case_insensitive";

explain analyze select name COLLATE "case_insensitive" COLLATE "C" COLLATE "case_insensitive", count(*)
from my_table group by name COLLATE "case_insensitive" COLLATE "C" COLLATE "case_insensitive";

-- In v0, the first call to examine_variable() directly uses the underlying
-- Var and its regular statistics.
-- In v3, the first call to examine_variable() fails to find a match.
-- On the second call, the underlying Var is used to call examine_variable()
-- again, which finds the regular statistics for the underlying Var.
-- This serves as a fallback mechanism.
explain analyze select address COLLATE "POSIX", count(*)
from my_table group by address COLLATE "POSIX";

regards,
--
ZizhuanLiu (X-MAN) 
44973863(at)qq(dot)com