RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Sunday, August 22, 2021 13:51
> To: Justin Pryzby <pryzby(at)telsasoft(dot)com>
> Cc: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>; ldh(at)laurent-hasson(dot)com;
> pgsql-performance(at)postgresql(dot)org
> Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2
> and 13.4
>
> Justin Pryzby <pryzby(at)telsasoft(dot)com> writes:
> > This looks useful, thanks. It seems like maybe win64 builds are very
> > slow running this:
>
> > exec_stmt_block() /
> > BeginInternalSubTransaction() /
> > AbortSubTransaction() /
> > reschedule_timeouts() /
> > schedule_alarm() /
> > setitimer() /
> > pg_timer_thread() /
> > WaitForSingleObjectEx ()
>
> Hmm ... we should not be there unless there are active timeout events,
> which there aren't by default. I wonder whether either Ranier or
> Laurent have statement_timeout or some similar option enabled.
>
> I tried setting statement_timeout = '1 min' just to see if that would affect
> the results. It does, but only incrementally on my Linux box (on v13, the
> exception-causing query slows from ~13sec to ~14sec). It's possible that
> our Windows version of
> setitimer() is far slower, but that doesn't make a lot of sense really --- the
> client side of that just briefly takes a critical section. It shouldn't be
> blocking.
>
> Also, the Windows version (src/backend/port/win32/timer.c) hasn't
> changed at all since before v11. So even if it's slow, that doesn't tell us
> what changed.
>
> There is a patch in v14 (09cf1d522) that drastically reduces the rate at
> which we make setitimer() calls, which would likely be enough to fix any
> performance problem that may exist here.
> But it's still unclear what's different between v11 and v13.
>
> regards, tom lane

Hello Tom,

On both my clean 13.4 install and current 11.2 install, I have
#statement_timeout = 0 # in milliseconds, 0 is disabled

Note that the 13.4 clean install I gave last measurements for has all stock settings.

Thank you,
Laurent.