Original >From: Tom Lane <[email protected]> >Date: 2026-08-16 23:59 >To: ZizhuanLiu X-MAN <[email protected]> >Cc: jian he <[email protected]>, PostgreSQL-development ><[email protected]> >Subject: Re: examine_variable ignored CollateExpr > >"=?utf-8?B?Wml6aHVhbkxpdSBYLU1BTg==?=" <[email protected]> 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<[email protected]>, 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)
[email protected]
v3-0001-proper-handling-examine_variable-RelabelType.patch
Description: Binary data
