Re: examine_variable ignored CollateExpr

Original
>From: jian he <jian(dot)universality(at)gmail(dot)com>
>Date: 2026-01-13 10:23
>To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
>Subject: examine_variable ignored CollateExpr
>hi.
>
>ComputeIndexAttrs:
>src/backend/commands/indexcmds.c, line 1995:
> /*
> * Strip any top-level COLLATE clause. This ensures that we treat
> * "x COLLATE y" and "(x COLLATE y)" alike.
> */
> while (IsA(expr, CollateExpr))
> expr = (Node *) ((CollateExpr *) expr)->arg;
>
>That means in function examine_variable:
......

hi, jian, hackers
After reviewing the patch and doing some additional tests and debugging,
I have the following observations about examine_variable():

1.The input Node *node has already been processed by eval_const_expressions().

2.After strip_all_phvs_deep() is applied, adjacent RelabelTypes may be created,
either at the top level or within a deeper subtree.

@jian, in your current code, the processing starts only from the leading RelabelType.
Therefore, adjacent RelabelTypes occurring in non-leading positions, including deeper subtrees,
are not effectively stripped. I believe this may affect the expression that is subsequently
used for comparison with index expressions and extended-statistics expressions.

3.Regarding the current master code, I don't think it is reasonable to try to find a Var
by stripping leading adjacent RelabelTypes and then treat the result as equivalent to
a base Var if a match is found. I agree with Jian's observation on this point:
```C
while (IsA(basenode, RelabelType))
basenode = (Node *) ((RelabelType *) basenode)->arg;
```C

4.I also don't think it is reasonable to strip RelabelTypes from both
the index expressions and extended-statistics expressions before comparing them.

For index expressions, I have not yet found a way to construct an index expression
whose top-level node is a RelabelType. If anyone knows how to construct such an
index expression, I would greatly appreciate an example.

5.Based on the above observations, my current understanding is that
we should normalize the expression after stripping PlaceHolderVars, rather than
simply stripping RelabelTypes from the expression before doing the comparisons.

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.
*
* If the leading RelabelType and its underlying argument have the same
* type, typmod, and collation, and the underlying argument is a base Var,
* applyRelabelType() will reduce the RelabelType chain to that base Var.
* Thus, the resulting expression is equivalent to a plain base Var.
*
* Keep the stripped result in save_node rather than modifying the
* original node. The result is used to compare against indexprs and
* extended-statistics expressions, which are also processed by
* eval_const_expressions() and compared using equal().
*/

I have included below the test SQL cases that I have come up with so far.
Some of the scenarios still need to be verified with GDB.

I understand that this functionality is quite important, and I realize that
my current testing is not yet comprehensive enough. There may also be
some misunderstandings or unreasonable assumptions in my analysis.

I would greatly appreciate any feedback, especially counterexamples or
additional test cases that could challenge my current understanding.
I will continue to improve the analysis and tests based on the feedback.

Thanks!

```SQL
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/v1 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.
-- v1 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 v1, 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