Re: BUG #17564: Planner bug in combination of generate_series(), unnest() and ORDER BY

I wrote:
> FWIW, this reproduces the bug for me in v13 and v14, but not v15 or HEAD.

A bit of bisecting later, I find that the behavior changed at

commit db0d67db2401eb6238ccc04c6407a4fd4f985832
Author: Tomas Vondra <tomas.vondra@postgresql.org>
Date:   Thu Mar 31 00:09:11 2022 +0200

    Optimize order of GROUP BY keys

I think that either that commit is buggy, or the commit message
omitted so many relevant facts as to verge on a lie.  The query
we are dealing with here has no GROUP BY whatsoever, but nonetheless
that commit produces a totally different plan (with a significantly
cheaper cost estimate) than its immediate predecessor.  I don't
see anything specifically about SRFs in that patch, but I think
what must have happened is that it changed cost estimates for
this scenario enough to accidentally cause selection of a
non-buggy plan.

The plan produced as of that commit, which is the same as what
you get in current HEAD, is

 Sort  (cost=2946.79..3096.79 rows=60000 width=36)
   Sort Key: (((unnest('{}'::jsonb[])) ->> 'timestamp'::text))
   ->  Result  (cost=90.08..1446.79 rows=60000 width=36)
         ->  ProjectSet  (cost=90.08..396.79 rows=60000 width=36)
               ->  Hash Join  (cost=90.08..96.19 rows=60 width=0)
                     Hash Cond: (groups.group_id = results.group_id)
                     ->  Seq Scan on groups  (cost=0.00..5.66 rows=166 width=4)
                     ->  Hash  (cost=89.33..89.33 rows=60 width=4)
                           ->  Index Only Scan using results_pkey on results  (cost=0.28..89.33 rows=60 width=4)
                                 Index Cond: (search_id = 3336)

I can't easily produce a nice EXPLAIN result for the previous plan,
since it fails in executor startup, but a heavily-trimmed pprint
dump is enough to show that it's totally different:

      {RESULT
      :startup_cost 4071.64
      :total_cost 5445.55
      :plan_rows 60000
      :lefttree
         {PROJECTSET
         :startup_cost 4071.64
         :total_cost 4395.55
         :plan_rows 60000
         :lefttree
            {NESTLOOP
            :startup_cost 4071.64
            :total_cost 4094.95
            :plan_rows 60
            :lefttree
               {GATHERMERGE
               :startup_cost 4071.48
               :total_cost 4078.32
               :plan_rows 60
               :lefttree
                  {SORT
                  :startup_cost 3071.47
                  :total_cost 3071.56
                  :plan_rows 35
                  :lefttree
                     {SEQSCAN
                     :startup_cost 0.00
                     :total_cost 3070.57
                     :plan_rows 35
                     :parallel_aware true
                     :parallel_safe true
                     :targetlist (
                        ...
                        {TARGETENTRY
                        :expr
                           {OPEXPR
                           :opno 3477
                           :opfuncid 3214
                           :opresulttype 25
                           :opretset false
                           :opcollid 100
                           :inputcollid 100
                           :args (
                              {FUNCEXPR
                              :funcid 2331
                              :funcresulttype 3802
                              :funcretset true    -- OOPS
                     ...
                     :scanrelid 1    -- this scan is on "results"
                     ...
               }
            :righttree
               {MEMOIZE
               :startup_cost 0.15
               :total_cost 0.31
               :plan_rows 1
               :lefttree
                  {INDEXONLYSCAN
                  :startup_cost 0.14
                  :total_cost 0.30
                  :plan_rows 1
                  :scanrelid 2       -- this scan is on "groups"
                  }
               :righttree <>
               }

So I'd like to know why an ostensibly unrelated commit changed
cost estimates by 43% for a query that hasn't even got a GROUP BY.

            regards, tom lane