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

Hoi Tom,

On Wed, 3 Aug 2022 at 16:56, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Richard Guo <guofenglinux(at)gmail(dot)com> writes:
> > On Tue, Aug 2, 2022 at 4:50 PM Martijn van Oosterhout <kleptog(at)gmail(dot)com
> >
> > wrote:
> >> Now it's morning I've thought of a way to reproduce it more easily, see
> >> the attached script.
>
> > Thanks for the report! I can reproduce it on HEAD.
>
> FWIW, this reproduces the bug for me in v13 and v14, but not v15 or HEAD.
> While the method to fix the bug seems clear enough, it doesn't seem
> like we have a test case that's stable enough to be worth anything
> as a regression test. Hmmm...
>
>
Looking at what Richard has uncovered, it appears the issue is related to
the table simply being big enough that it considers a parallel seqscan
plan, and then fails. Something I can look into in the morning.

The part I haven't seen explained is why the generate_series() is
important. My guess is that if you replace it with an expression it is no
longer an SRF and it produces some completely different plan that prevents
the problematic path being triggered.

Nice that the problem has been found!
--
Martijn van Oosterhout <kleptog(at)gmail(dot)com> http://svana.org/kleptog/