Re: enable_incremental_sort changes query behavior - Mailing list pgsql-hackers

On Fri, Oct 2, 2020 at 2:25 PM Tomas Vondra
<tomas.vondra@2ndquadrant.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