Re: Converting SetOp to read its two inputs separately

On Thu, Nov 14, 2024 at 11:00 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Aside from that minor TODO, the main thing that's left undone in this
> patch series is to persuade the thing to exploit presorted input
> paths. Right now it fails to do so, as can be seen in some of the
> regression test cases, eg
>
> regression=# set enable_hashagg = 0;
> SET
> regression=# explain (costs off) select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
> QUERY PLAN
> ------------------------------------------------------------------
> SetOp Except
> -> Sort
> Sort Key: tenk1.unique1
> -> Index Only Scan using tenk1_unique1 on tenk1
> -> Sort
> Sort Key: tenk1_1.unique2
> -> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
> Filter: (unique2 <> 10)
> (8 rows)
>
> Obviously the sorts are unnecessary, but it's not seeing that.
> I suppose this requires integrating generate_nonunion_paths with
> the logic from commit 66c0185a3. I tried to make sense of that,
> but failed --- either I don't understand it, or there are a
> number of things wrong with it. I'd welcome some help with
> getting that done.

I think we may need to do the following to make this work:

1. We need to teach set_operation_ordered_results_useful() that sorted
input paths are also useful for INTERSECT/EXCEPT, so that we can have
setop_pathkeys set for the subqueries.

2. In generate_nonunion_paths(), we need to provide a valid
"interesting_pathkeys" when calling build_setop_child_paths().

Thanks
Richard