Re: BUG #18652: Planner can not find pathkey item to sort for query with expression and expression index

On Thu, Oct 10, 2024 at 5:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It looks like we are generating a Path tree in which one of the
> inputs to a MergeAppend is a plain unsorted seqscan, which'd
> be all right except it doesn't expose the required sort value
> in its targetlist.

Correct. In addition, find_computable_ec_member() fails to find a
computable expression from its targetlist.

Here is a slightly simpler repro query.

SELECT i + 0 AS c FROM (SELECT i FROM t UNION ALL SELECT i + 1 FROM t)
ORDER BY c;
ERROR: could not find pathkey item to sort

I think the expected plan should look like:

EXPLAIN (VERBOSE, COSTS OFF)
SELECT i + 0 AS c FROM (SELECT i FROM t UNION ALL SELECT i + 1 FROM t)
ORDER BY c;
QUERY PLAN
------------------------------------------------------------
Result
Output: ((t.i + 0))
-> Merge Append
Sort Key: ((t.i + 0))
-> Index Scan using t_expr_idx on public.t
Output: t.i, (t.i + 0)
-> Sort
Output: ((t_1.i + 1)), (((t_1.i + 1) + 0))
Sort Key: (((t_1.i + 1) + 0))
-> Seq Scan on public.t t_1
Output: (t_1.i + 1), ((t_1.i + 1) + 0)
(11 rows)

For the indexscan path, find_computable_ec_member() is able to find
(t.i + 0) which can be computed from its tlist item 't.i'.

For the seqscan path, though, find_computable_ec_member() is not able
to find ((t_1.i + 1) + 0) from its tlist item '(t_1.i + 1)'.

I think this is because find_computable_ec_member() only tries to
match Vars. Maybe we should teach it to also match OpExprs?

Thanks
Richard