> On Nov 8, 2023, at 8:26 PM, Lincoln Swaine-Moore <lswainemoore@xxxxxxxxx> 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 number of rows (4 - 24) but the planner estimated it would emit 1000 rows because that's Postgres' default in absence of other info. (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 case the 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 as above) to better inform the planner. In my case I used ROWS 16 since that was relatively accurate -- a lot more accurate than 1000, anyway. Then I found that my pure SQL custom function was getting inlined, which caused the information in the ROWS qualifier to get 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 got lucky, and that the use of LATERAL sent the planner down a better path. Hope this is at least a little helpful! Good luck, Philip
