Re: enable_incremental_sort changes query behavior

On Fri, Oct 2, 2020 at 2:25 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
> The backtrace looks like this:
>
> #0 get_sortgroupref_tle
> #1 0x0000000000808ab9 in prepare_sort_from_pathkeys
> #2 0x000000000080926c in make_sort_from_pathkeys
> #3 0x0000000000801032 in create_sort_plan
> #4 0x00000000007fe7e0 in create_plan_recurse
> #5 0x0000000000800b2c in create_gather_merge_plan
> #6 0x00000000007fe94d in create_plan_recurse
> #7 0x0000000000805328 in create_nestloop_plan
> #8 0x00000000007ff3c5 in create_join_plan
> #9 0x00000000007fe5f8 in create_plan_recurse
> #10 0x0000000000800d68 in create_projection_plan
> #11 0x00000000007fe662 in create_plan_recurse
> #12 0x0000000000801252 in create_upper_unique_plan
> #13 0x00000000007fe760 in create_plan_recurse
> #14 0x00000000007fe4f2 in create_plan
> #15 0x000000000081082f in standard_planner
>
> and the create_sort_plan works with lefttree that is IndexScan, so the
> query we're constructing looks like this:
>
> Distinct
> -> Nestloop
> -> Gather Merge
> -> Sort
> -> Index Scan
>
> and it's the sort that expects to find the expression in the Index Scan
> target list. Which seems rather bogus, because clearly the index scan
> does not include the expression. (I wonder if it's somehow related that
> indexes can't be built on volatile expressions ...)
>
> Anyway, the index scan clearly does not include the expression the sort
> references, hence the failure. And the index can can't compute it,
> because we probably need to compute it on top of the join I think
> (otherwise we might get duplicate values for volatile functions etc.)
>
>
> Looking at this from a slightly different angle, the root cause here
> seems to be that generate_useful_gather_paths uses the pathkeys it gets
> from get_useful_pathkeys_for_relation, which means root->query_pathkeys.
> But all other create_gather_merge_calls use root->sort_pathkeys, so
> maybe this is the actual problem and get_useful_pathkeys_for_relation
> should use root->sort_pathkeys instead. That does fix the issue for me
> too (and it passes all regression tests).

So I've been a bit confused how our error could come from working with
root->query_pathkeys when that's what's supposedly being set from the
make_pathkeys_for_sortclauses() call in the backtrace Jaime reported,
but I just realized that the trace I get when reproducing the error is
different -- and matches the one you shared above.

Jaime: was the backtrace in the original report by any chance record
from breakpointing in the first call to get_sortgroupref_tle() (and
one that successfully returned a sort group ref) rather than a call
that hit the elog error on line 379?

James