Re: BUG #16390: Regression between 12.2 and 11.6 on a recursive query : very slow and overestimation of rows

PG Bug reporting form <noreply@postgresql.org> writes:
> Everything started on a big recursive query working fine with a filled
> database (all tables contain at least a few rows). It executes in ~120ms.
> But the same query, on a nearly empty DB takes ~10000ms on 12.2 and still
> ~100ms on 11.6 (same DB, vacuumed full and analyzed).

FWIW, I get largely the same plan and runtime (~5ms) for this example on
11.x, 12.x, and HEAD, when using non-JIT-enabled builds.  It looks like
most of your runtime on 12.x is going into JIT compilation:

>  JIT:
>    Functions: 36
>    Options: Inlining true, Optimization true, Expressions true, Deforming
> true
>    Timing: Generation 9.996 ms, Inlining 12.520 ms, Optimization 116.679 ms,
> Emission 57.894 ms, Total 197.090 ms

It's not too surprising that the system thinks that compiling the query
would be worth the effort, since it's overestimating the size of the
recursive union so much.  But this toy example doesn't really prove
anything about that --- the misestimate is mostly because the planner
won't believe that an empty table is empty.  Which most of the time is a
good safety feature, even if it goes off the rails in this particular
case.

In short: as is so often the case, examining the behavior on toy tables
is not going to tell much about your real problem.  Please see

https://wiki.postgresql.org/wiki/Slow_Query_Questions

for guidance about better ways to identify performance problems.
And take the issue to pgsql-performance, because it's unlikely
there's a bug here.

            regards, tom lane