On Tue, Nov 18, 2025 at 5:27 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Alternatively, we could teach match_index_to_operand to unwrap PHV
> expressions when matching them to indexes. This would also allow the
> WHERE clause to match indexes for non-Var expressions, which is an
> improvement over the behavior prior to v18. But I haven't fully
> thought through whether this approach is safe in all cases,
> particularly when outer joins are involved.
I believe this approach is safe, because a PlaceHolderVar appearing in
a relation-scan-level expression is effectively a no-op. To play it
safe, we could verify that the PlaceHolderVar is not marked nullable
and that its syntactic scope matches this index, and only strip such
PlaceHolderVars (though it's unclear if this is really necessary).
Attached is a patch that implements this approach. With this patch,
the reported query can use an index scan again. Additionally, it
enables planner to explore index scans in other cases that were
previously not possible, even before v18. For example:
create table t (a int);
insert into t select i from generate_series(1,1000)i;
create index on t (coalesce(a, 1));
analyze t;
explain (costs off)
select * from t t1 left join
(select coalesce(a, 1) as c, * from t t2) s on true
where t1.a > s.c;
QUERY PLAN
-----------------------------------------------
Nested Loop
-> Seq Scan on t t1
-> Index Scan using t_coalesce_idx on t t2
Index Cond: (COALESCE(a, 1) < t1.a)
(4 rows)
The expression coalesce(a, 1) is wrapped in a PlaceHolderVar, and
previously it was not possible to match it to the index expression.
- Richard
