Re: examine_variable ignored CollateExpr

Original
>From: shihao zhong <zhong950419(at)gmail(dot)com>
>Date: 2026-10-05 11:59
>To: ZizhuanLiu X-MAN <44973863(at)qq(dot)com>
>Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, jian he <jian(dot)universality(at)gmail(dot)com>, tgl <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Guo <rguo(at)postgresql(dot)org>
>Subject: Re: examine_variable ignored CollateExpr
>Hi Jian,
>
>With v1 or v4 I get a worse estimate when the table has no
>statistics object.
>
> CREATE TABLE t (a text);
> INSERT INTO t SELECT chr(65 + g % 52)
> FROM generate_series(1, 5200) g;
> ANALYZE t;
> EXPLAIN SELECT * FROM t WHERE a COLLATE "C" = 'A';
>
>On master I get
> Seq Scan on t (cost=0.00..89.00 rows=100 width=2)
>With v1 or v4 I get
> Seq Scan on t (cost=0.00..89.00 rows=26 width=2)
>
>Could we keep the stripping, and check the statistics expressions
>first only when a COLLATE was stripped from a plain column? A
>table without extended statistics pays one test of rel->statlist.
>
>Thanks,
>Shihao

Hi, Shihao
Thank you for your feedback!

Hi, all

Fix RelabelType handling in statistics lookup

Regarding examine_variable() in src/backend/utils/adt/selfuncs.c:
1. Handling adjacent RelabelType nodes after stripping PHVs
PATCH[1] added PHV stripping for node, followed by the following loop on
the resulting basenode:
```C
while (IsA(basenode, RelabelType))
basenode = (Node *) ((RelabelType *) basenode)->arg;
```C
This loop strips only consecutive RelabelType nodes at the top level.
It does not handle adjacent RelabelType nodes that may arise deeper
in the expression tree.

Since stripping PHVs, as introduced by PATCH[1], can bring previously
separated RelabelType nodes into adjacency, I think it would be better to
strip these adjacent nodes at the point where this adjacency can arise.
This keeps the creation and removal of adjacent RelabelType nodes together
as a single, cohesive operation, without requiring a heavyweight function.

I therefore chose to call the new helper function strip_all_adjacency_relabeltypes()
from within strip_all_phvs_mutator().

(Sorry, my previous version of strip_all_adjacency_relabeltypes()
did not actually return the stripped expression.)

2. Preserving the previous lookup behavior
Before PATCH[1], there was a symmetric attempt to strip the topmost RelabelType
from the input node, index expressions, and extended statistics expressions
before comparing them. The relevant code is as follows:
```C
--for node
if (IsA(node, RelabelType))
basenode = (Node *) ((RelabelType *) node)->arg;
else
basenode = node;

--for index's expr
if (indexkey && IsA(indexkey, RelabelType))
indexkey = (Node *) ((RelabelType *) indexkey)->arg;

--for extended statistics
if (expr && IsA(expr, RelabelType))
expr = (Node *) ((RelabelType *) expr)->arg;
```C

The original behavior was to strip the topmost RelabelType before comparing
expressions with equal().

However, this is not entirely satisfactory. Ideally, we should use statistics
associated with expressions that match exactly according to equal().
Stripping the topmost RelabelType before comparison should be a fallback
rather than the preferred approach.

Therefore, the new PATCH first compares the input expression, index expressions,
and extended statistics expressions as they are, without stripping their topmost
RelabelType nodes. If no matching statistics are found, it retries the lookup under
certain conditions, symmetrically stripping the topmost RelabelType from the
input expression and the candidate expressions before comparing them.
This preserves the behavior that existed before PATCH[1] as a fallback, maintaining
compatibility with the previous lookup logic.

(The normalization performed by strip_all_phvs_mutator(), following logic similar to
applyRelabelType(), is a separate matter and remains appropriate. It normalizes
adjacent RelabelType nodes that may arise when PHVs are stripped, while preserving
the semantics of the outermost relabeling. This is consistent with the normalization
already applied to index expressions and extended statistics expressions.)

Feedback is welcome.

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7e9f852a79fe19d4d0f18aabc32a620797fb676e
[1] https://www.postgresql.org/message-id/flat/E1va3GH-003FBe-35%40gemulon.postgresql.org

The following are the relevant test SQL statements:
1. After strip_all_phvs_mutator() and strip_all_adjacency_relabeltypes(),
statistics are successfully found for the corresponding index expressions
and extended statistics expressions:
--setup
DROP TABLE IF EXISTS phv_left, phv_right CASCADE;
DROP COLLATION IF EXISTS case_insensitive;

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

CREATE TABLE phv_left ( id int );

CREATE TABLE phv_right( id int, name text COLLATE "C");

INSERT INTO phv_left
SELECT g
FROM generate_series(1, 1100) AS g;

INSERT INTO phv_right
SELECT g,
CASE WHEN g % 17 = 0 THEN NULL
ELSE chr(65 + (g % 26))
END
FROM generate_series(1, 1000) AS g;

-- Matches the statistics for index phv_right_index_5
create index phv_right_index_5 on phv_right(COALESCE(name COLLATE "C", ''::text COLLATE "C"));
analyze phv_right;

EXPLAIN analyze
SELECT r.x COLLATE "C" AS k,
count(*)
FROM phv_left AS l
LEFT JOIN
(
SELECT id,
(COALESCE(name COLLATE "C", ''::text COLLATE "C") COLLATE case_insensitive) AS x
FROM phv_right
) AS r
ON r.id = l.id
GROUP BY r.x COLLATE "C";

-- Matches the statistics for extended statistics object phv_right_expr_stats_5
drop index phv_right_index_5; --drop phv_right_index_5 first
create STATISTICS phv_right_expr_stats_5
on (COALESCE(name COLLATE "C", ''::text COLLATE "C"))
FROM phv_right; ANALYZE phv_right;
analyze phv_right;

EXPLAIN analyze
SELECT r.x COLLATE "C" AS k,
count(*)
FROM phv_left AS l
LEFT JOIN
(
SELECT id,
(COALESCE(name COLLATE "C", ''::text COLLATE "C") COLLATE case_insensitive) AS x
FROM phv_right
) AS r
ON r.id = l.id
GROUP BY r.x COLLATE "C";

2. Statistics for the underlying Var are found after stripping the topmost RelabelType from basenode on the second attempt:
CREATE TABLE t (a text);
INSERT INTO t SELECT chr(65 + g % 52)
FROM generate_series(1, 5200) g;
ANALYZE t;
EXPLAIN SELECT * FROM t WHERE a COLLATE "C" = 'A';

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