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(dot)vondra(at)postgresql(dot)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