> On Nov 8, 2023, at 8:26 PM, Lincoln Swaine-Moore <lswainemoore@gmail.com> wrote:
>
> Hi all--
>
> I'm having a performance problem in 12.16 that I'm hoping someone can help with.
<much useful info snipped>
> Thanks for any and all help and suggestions.
Hi Lincoln,
I haven't read your SQL carefully so I may be completely off base, but I wanted to share an experience I had with
generate_series()that caused some planner headaches that may be affecting you too.
I was using generate_series() in a CROSS JOIN with a large table. The call to generate_series() only emitted a small
numberof rows (4 - 24) but the planner estimated it would emit 1000 rows because that's Postgres' default in absence of
otherinfo. (See https://www.postgresql.org/docs/current/sql-createfunction.html, "The default assumption is 1000
rows.")I see an estimate for 1000 rows in your EXPLAIN output too, so you're experiencing the same although in your
casethe estimate of 1000 might be more accurate. The misestimation was causing significant performance problems for me.
My solution was to wrap generate_series() in a custom function that had a ROWS qualifier (documented at the same link
asabove) to better inform the planner. In my case I used ROWS 16 since that was relatively accurate -- a lot more
accuratethan 1000, anyway.
Then I found that my pure SQL custom function was getting inlined, which caused the information in the ROWS qualifier
toget lost. :-) I rewrote it in PL/pgSQL to prevent the inlining, and that solution worked well for me. (See convo at
https://www.postgresql.org/message-id/flat/76B16E5F-59D0-4C97-8DBA-4B3BB21E2009%40americanefficient.com)
On another note, I have also seen unexpected performance gains from introducing LATERAL into a JOIN. My guess is that I
gotlucky, and that the use of LATERAL sent the planner down a better path.
Hope this is at least a little helpful!
Good luck,
Philip