From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
cc: Richard Guo <guofenglinux@gmail.com>,
        Martijn van Oosterhout <kleptog@gmail.com>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17564: Planner bug in combination of generate_series(),
 unnest() and ORDER BY
In-reply-to: <3002973.1659538573@sss.pgh.pa.us>
References: <17564-c7472c2f90ef2da3@postgresql.org>
 <2184335.1659392231@sss.pgh.pa.us>
 <CADWG95s03YZsRy3+q0bQFXW_v_f-9vMvcFOo6KUL08DvqbXhqg@mail.gmail.com>
 <CAMbWs49AgGUophtyThqHhSUyDTu3h6oATg-7GgowCtvHWCh9qQ@mail.gmail.com>
 <3002973.1659538573@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 03 Aug 2022 10:56:13 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3109431.1659556220.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 03 Aug 2022 15:50:20 -0400
Message-ID: <3109432.1659556220@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3109432.1659556220%40sss.pgh.pa.us>
Precedence: bulk

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=3D2946.79..3096.79 rows=3D60000 width=3D36)
   Sort Key: (((unnest('{}'::jsonb[])) ->> 'timestamp'::text))
   ->  Result  (cost=3D90.08..1446.79 rows=3D60000 width=3D36)
         ->  ProjectSet  (cost=3D90.08..396.79 rows=3D60000 width=3D36)
               ->  Hash Join  (cost=3D90.08..96.19 rows=3D60 width=3D0)
                     Hash Cond: (groups.group_id =3D results.group_id)
                     ->  Seq Scan on groups  (cost=3D0.00..5.66 rows=3D166=
 width=3D4)
                     ->  Hash  (cost=3D89.33..89.33 rows=3D60 width=3D4)
                           ->  Index Only Scan using results_pkey on resul=
ts  (cost=3D0.28..89.33 rows=3D60 width=3D4)
                                 Index Cond: (search_id =3D 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