Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Martijn van Oosterhout <kleptog@gmail.com>
Cc: Richard Guo <guofenglinux@gmail.com>, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Date: 2022-08-03T21:53:30Z
Lists: pgsql-bugs 
Martijn van Oosterhout <kleptog@gmail.com> writes:
> 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.

I think the generate_series() forces a ProjectSet to be put atop the
join, where we'd probably not have done that without it, having made
the bogus assumption that we could evaluate the sort-key SRF at scan
level.  Why that changes the costs enough to mask or not mask the bug
is still obscure.

			regards, tom lane

Commits

  1. Add proper regression test for the recent SRFs-in-pathkeys problem.

  2. Fix incorrect tests for SRFs in relation_can_be_sorted_early().

  3. Optimize order of GROUP BY keys