RE: Performance degradation after upgrading from 9.5 to 14

[Zahir Lalani]

Same issue and took us ages to work out that is was JIT! The default is on, and 
setting off solves the problem. I have seen several blogs reporting the same 
and so wonder why this default is on?

Z

From: Олександр Янін 
Sent: Monday, April 22, 2024 8:01 PM
To: Johnathan Tiamoh 
Cc: pgsql-generallists.postgresql.org 
Subject: Re: Performance degradation after upgrading from 9.5 to 14

You don't often get email from 
aleksandr.ja...@privatbank.ua. Learn why 
this is important

CAUTION: This email originated from outside of the organisation. Do not click 
links or open attachments unless you recognise the sender and know the content 
is safe. Visit the information security portal (MetaCompliance - MyCompliance 
Cloud)
 to do your training.

Try setting enable_memoize to off.
Our practice has shown that enabling this parameter by default often resulted 
in less than optimal query plans in the cache.


ср, 17 апр. 2024 г. в 20:13, Johnathan Tiamoh 
mailto:johnathantia...@gmail.com>>:
Hello,


I performed an  upgrade from postgresql-9.5 to postgresql-14 and the 
performance has degraded drastically.

Please, is they any advice on getting performance back ?

RE: Performance degradation after upgrading from 9.5 to 14

[Andreas Joseph Krogh]

På lørdag 27. april 2024 kl. 11:46:26, skrev Zahir Lalani <
ZahirLalani@oliver.agency >:

Same issue and took us ages to work out that is was JIT! The default is on, 
and setting off solves the problem. I have seen several blogs reporting the 
same and so wonder why this default is on?



I can confirm this, even in v16 we've turned JIT off.






--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

Monitoring and debugging historical performance

[sud]

Hi All,
While looking option to see how one can monitor database performance for
debugging into historical database performance issues (historical database
wait events , CPU/memory utilization in past, query execution times in past
, executions paths at any point in time in past etc), It appears there
exists some options as i googled in the internet like
pg_sentinel,pgsnapper, pg_collector. Wanted to check, if anybody used these
utilities and suggest any of them to use for a longer term use?

Regards
Sud

Re: Monitoring and debugging historical performance

[Julien Rouhaud]

Hi,

On Sat, Apr 27, 2024 at 10:01 PM sud  wrote:
>
> Hi All,
> While looking option to see how one can monitor database performance for 
> debugging into historical database performance issues (historical database 
> wait events , CPU/memory utilization in past, query execution times in past , 
> executions paths at any point in time in past etc), It appears there exists 
> some options as i googled in the internet like pg_sentinel,pgsnapper, 
> pg_collector. Wanted to check, if anybody used these utilities and suggest 
> any of them to use for a longer term use?

I would personally recommend PoWA
(https://powa.readthedocs.io/en/latest/), as it handles everything you
require.  Disclairmer: I'm the main author of this tool

Stess test via libpq for postgreSQL DB

[Sasmit Utkarsh]

Hi Postgresql Team,

Could you please help on how to get started with some stress test for my
application which uses libpq to interact with postgres flexi db. I tried to
explore pgbench which is a builtin tool to get some stats. But i have some
clarifications if we can use it to execute PLSQL code blocks rather than
standalone SQL queries. Please advise


Regards,
Sasmit Utkarsh
+91-7674022625

Re: Stess test via libpq for postgreSQL DB

[David G. Johnston]

On Sat, Apr 27, 2024 at 11:07 AM Sasmit Utkarsh 
wrote:

>
> But i have some clarifications if we can use it to execute PLSQL code
> blocks rather than standalone SQL queries.
>

https://www.postgresql.org/docs/current/sql-do.html

David J.

Re: Stess test via libpq for postgreSQL DB

[Sasmit Utkarsh]

Thanks David


Regards,
Sasmit Utkarsh
+91-7674022625


On Sat, Apr 27, 2024 at 11:44 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Sat, Apr 27, 2024 at 11:07 AM Sasmit Utkarsh 
> wrote:
>
>>
>> But i have some clarifications if we can use it to execute PLSQL code
>> blocks rather than standalone SQL queries.
>>
>
> https://www.postgresql.org/docs/current/sql-do.html
>
> David J.
>
>

show fct_name of the function/procedure

[ft]

Hello, 

I need (a very simple construct) to show the fct_name/proc_name 
for the function/procedure.

Example:
create procedure/function bet_process() as 
$$
declare
 pid  int  := pg_backend_pid() ; --  it works fine
 fct_name text := pg_fct_name(); --  I need it
begin
 -- do s.th like 
 -- call log(pid, fct_name, 'my message');
end;
$$ language plpgsql;

Franz

Re: show fct_name of the function/procedure

[David G. Johnston]

On Saturday, April 27, 2024, ft  wrote:

>
>  fct_name text := pg_fct_name(); --  I need it
>
>

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

PG_ROUTINE_OID

I think casting that to “regproc” will get you what you need.  Otherwise
there should be functions or a query to perform the needed lookup.

https://www.postgresql.org/docs/current/datatype-oid.html

David J.