public inbox for [email protected]help / color / mirror / Atom feed
Re: Correct query for monitor 3+ messages / 3 participants [nested] [flat]
* Re: Correct query for monitor @ 2025-09-26 20:15 veem v <[email protected]> 0 siblings, 2 replies; 3+ messages in thread From: veem v @ 2025-09-26 20:15 UTC (permalink / raw) To: Ron Johnson <[email protected]>; pgsql-general <[email protected]> Thank you so much for the quick response. I have a follow up question on this as below, If we want to identify, what exact query inside a procedure is taking a longer time:- Using any pg_* views, Is there an easy way to tie the query_id of the procedure with the query_ids of the internal sqls(those are executed within the procedure) to quickly get the culprit sql? And say , we got the sql and saw a bad plan and we want to change the plan or attach a good plan to that query , is there a possible way to do that in postgres? On Fri, 26 Sept 2025 at 18:53, Ron Johnson <[email protected]> wrote: > They seem reasonable to me. My stance is "run it until you want more > features, or find a flaw." > > On Fri, Sep 26, 2025 at 6:23 AM veem v <[email protected]> wrote: > >> Hello, >> We want to have monitoring on three things 1) If the database restarted >> or went down in the last few hours? 2)If the connections are high 3) High >> tablespace growth . Want to understand , if we can utilize below queries >> for the same or any flaws in this strategy? >> >> 1)SELECT >> CASE >> WHEN now() - pg_postmaster_start_time() < interval '12 hours' >> THEN 'ALERT: DB was restarted in the last 12 hours' >> ELSE 'OK' >> END AS status; >> >> 2)SELECT >> CASE >> WHEN conn_count > max_conn * 0.8 THEN >> 'ALERT: Connection usage is above 80%' >> ELSE >> 'OK: Connection usage is under control' >> END AS status, >> conn_count AS current_connections, >> max_conn AS max_connections, >> ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used >> FROM ( >> SELECT >> COUNT(*) AS conn_count, >> (SELECT setting::int FROM pg_settings WHERE name = >> 'max_connections') AS max_conn >> FROM pg_stat_activity >> ) sub; >> >> 3)SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size >> FROM pg_tablespace; >> >> Regards >> Veem >> > > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Correct query for monitor @ 2025-09-26 23:00 Ron Johnson <[email protected]> parent: veem v <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Ron Johnson @ 2025-09-26 23:00 UTC (permalink / raw) To: pgsql-general <[email protected]> On Fri, Sep 26, 2025 at 4:15 PM veem v <[email protected]> wrote: > Thank you so much for the quick response. I have a follow up question on > this as below, > > If we want to identify, what exact query inside a procedure is taking a > longer time:- Using any pg_* views, Is there an easy way to tie the > query_id of the procedure with the query_ids of the internal sqls(those are > executed within the procedure) to quickly get the culprit sql? > Are there queries inside a cursor loop? > And say , we got the sql and saw a bad plan and we want to change the plan > or attach a good plan to that query , is there a possible way to do that in > postgres? > >> PG does not support hints or "attaching plans to queries". -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Correct query for monitor @ 2025-09-27 06:37 Laurenz Albe <[email protected]> parent: veem v <[email protected]> 1 sibling, 0 replies; 3+ messages in thread From: Laurenz Albe @ 2025-09-27 06:37 UTC (permalink / raw) To: veem v <[email protected]>; Ron Johnson <[email protected]>; pgsql-general <[email protected]> On Sat, 2025-09-27 at 01:45 +0530, veem v wrote: > If we want to identify, what exact query inside a procedure is taking a longer time: > - Using any pg_* views, Is there an easy way to tie the query_id of the procedure > with the query_ids of the internal sqls(those are executed within the procedure) > to quickly get the culprit sql? No, you have to read the function body. Then you can look for the statements therein in pg_stat_statements. > And say , we got the sql and saw a bad plan and we want to change the plan or attach > a good plan to that query , is there a possible way to do that in postgres? No, there isn't. You can use the pg_hint_plan extension and its query hints to force a certain execution plan. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-27 06:37 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-26 20:15 Re: Correct query for monitor veem v <[email protected]> 2025-09-26 23:00 ` Ron Johnson <[email protected]> 2025-09-27 06:37 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox