Pavel Stehule <pavel.stehule@gmail.com> writes:
> In Czech discussion group was reported performance regression of CTE
> query. I wrote a test, when I can show it.
I don't see anything tremendously wrong here. The older branches are
choosing the right plan for entirely wrong reasons: they don't notice
that "select foo(a) from pl" has a set-returning function in the
targetlist and so don't adjust the estimated number of result rows
for that. In this particular example, foo() seems to return an average
of about 11 rows per call, versus the default estimate of 1000 rows per
call, so the size of the result is overestimated and that dissuades
the planner from using a hashed subplan. But the error could easily
have gone the other way, causing the planner to use a hashed subplan
when it shouldn't, and the consequences of that are even worse. So
I don't think that ignoring SRFs in the targetlist is better.
If you add "ROWS 10" or so to the declaration of the function, you
get a better row estimate and it goes back to the hashed subplan.
regards, tom lane