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: <3109432.1659556220@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> <3109432.1659556220@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Wed, 03 Aug 2022 15:50:20 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3242057.1659563056.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 03 Aug 2022 17:44:17 -0400
Message-ID: <3242058.1659563057@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3242058.1659563057%40sss.pgh.pa.us>
Precedence: bulk

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

I decided I'd wasted entirely too much time trying to find a suitable
test case, so I pushed the bug fix with no new regression test.

Meanwhile, back at the question of whether db0d67db2 is buggy,
it looks like that reduces to whether it was intentional that that
made a large change in estimated sort costs.  What I'm getting from
v15 and HEAD, as I said earlier, 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)

After applying this patch, what I get from v13 and v14 is

 Sort  (cost=3D6208.59..6358.59 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)

So this plan is identical except for the sort costs, which seem to
be about half of what they were in the older branches.  If that was
intentional, why didn't the commit message mention it?  It's not
exactly a minor change, and enable_group_by_reordering doesn't
seem to have any effect on it.

			regards, tom lane