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