public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pär Mattsson <[email protected]>
To: [email protected]
Cc: Pgsql-admin <[email protected]>
Subject: Re: Performance issue!
Date: Mon, 22 Dec 2025 17:38:13 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
--Apple-Mail-9E4870C3-42C8-4BB8-9532-EE48E258CC61
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi! It is Postgres version 16.<div>Thanks a=
lot for the answer!<br id=3D"lineBreakAtBeginningOfSignature"><div dir=3D"lt=
r">Regards P=C3=A4r<div>Mobile: +46 70 606 96 45</div></div><div dir=3D"ltr"=
><br><blockquote type=3D"cite">22 dec. 2025 kl. 17:26 skrev CEO Goodfellas H=
oldings LLC <[email protected]>:<br><br></blockquote></div><blockq=
uote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<div>Hi everyone, I=E2=80=99m g=
onna answer the gentleman. Maybe I can share some wealth . </div><div>T=
he disclaimer is I have a strong AI background and I=E2=80=99m running a per=
sonal multi boat with API connections to five different providers and then I=
run the responses through vertex to ensure reliability. But we all know tha=
t it=E2=80=99s always a capture so happy holidays and hope this helps!</div>=
<div dir=3D"ltr"><br></div><p style=3D"line-height: normal; margin: 0px 0px 1=
2px;"> <span style=3D"font-family: "Times New Roman"; font-si=
ze: 19px; color: rgb(0, 0, 0);">Yep =E2=80=94 here are a few Postgres perfor=
mance script =E2=80=9Cbundles=E2=80=9D I=E2=80=99ve used in real life that p=
roduce clean, executive-friendly output (and are safe to run in production i=
f you keep the sampling windows sane). They=E2=80=99re grouped by what stake=
holders usually ask: =E2=80=9CIs the DB slow?=E2=80=9D =E2=80=9CWhat=E2=80=99=
s slow?=E2=80=9D =E2=80=9CWho=E2=80=99s causing it?=E2=80=9D =E2=80=9CIs it I=
O/CPU/locks?=E2=80=9D =E2=80=9CIs it indexes?=E2=80=9D</span></p><p style=3D=
"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=
=3D"font-family: "Times New Roman"; font-size: 12px; color: rgb(0,=
0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px; min-he=
ight: 13.8px;"><span style=3D"font-family: "Times New Roman"; font=
-size: 12px; color: rgb(128, 128, 128);"><br></span></p><p style=3D"line-hei=
ght: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-=
family: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);">=
<br></span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-he=
ight: 13.8px;"><span style=3D"font-family: "Times New Roman"; font=
-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: n=
ormal; margin: 0px 0px 14.9px;"><span style=3D"font-family: TimesNewRomanPS-=
BoldMT; font-size: 24px; color: rgb(0, 0, 0);"><b>1) =E2=80=9CWhat=E2=80=99s=
happening right now?=E2=80=9D (top waits, locks, long queries)</b></span></=
p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;=
"><span style=3D"font-family: "Times New Roman"; font-size: 12px; c=
olor: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin:=
0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Times N=
ew Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p sty=
le=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span s=
tyle=3D"font-family: "Times New Roman"; font-size: 12px; color: rg=
b(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px=
14px;"><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 21px;=
color: rgb(0, 0, 0);"><b>1A. Active sessions + wait reasons (quick triage)<=
/b></span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-hei=
ght: 13.8px;"><span style=3D"font-family: "Times New Roman"; font-=
size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: no=
rmal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; co=
lor: rgb(0, 0, 0);">SELECT</span></p><p style=3D"line-height: normal; margin=
: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0=
, 0);"> now() AS ts,</span></p><p style=3D"line-height: normal; margin=
: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0=
, 0);"> pid,</span></p><p style=3D"line-height: normal; margin: 0px;">=
<span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">=
usename,</span></p><p style=3D"line-height: normal; margin: 0px;"><sp=
an style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&nb=
sp; application_name,</span></p><p style=3D"line-height: normal; margin: 0px=
;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0)=
;"> client_addr,</span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);"> state,</span></p><p style=3D"line-height: normal; margin: 0px;"><=
span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&=
nbsp; wait_event_type,</span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);"> wait_event,</span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);"> date_trunc('second', now() - query_start) AS runtime,</span></p><=
p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Co=
urier; font-size: 22px; color: rgb(0, 0, 0);"> left(regexp_replace(que=
ry, '\s+', ' ', 'g'), 140) AS query_140</span></p><p style=3D"line-height: n=
ormal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; c=
olor: rgb(0, 0, 0);">FROM pg_stat_activity</span></p><p style=3D"line-height=
: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px=
; color: rgb(0, 0, 0);">WHERE pid <> pg_backend_pid()</span></p><p sty=
le=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier=
; font-size: 22px; color: rgb(0, 0, 0);"> AND state <> 'idle'</s=
pan></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-f=
amily: Courier; font-size: 22px; color: rgb(0, 0, 0);">ORDER BY (now() - que=
ry_start) DESC</span></p><p style=3D"line-height: normal; margin: 0px;"><spa=
n style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">LIMI=
T 30;</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-h=
eight: 13.8px;"><span style=3D"font-family: "Times New Roman"; fon=
t-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: n=
ormal; margin: 0px 0px 14px;"><span style=3D"font-family: TimesNewRomanPS-Bo=
ldMT; font-size: 21px; color: rgb(0, 0, 0);"><b>1B. Blocking / blocked chain=
(this one wins political battles)</b></span></p><p style=3D"line-height: no=
rmal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family:=
"Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></s=
pan></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-f=
amily: Courier; font-size: 22px; color: rgb(0, 0, 0);">WITH blocked AS (</sp=
an></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-fa=
mily: Courier; font-size: 22px; color: rgb(0, 0, 0);"> SELECT</span></=
p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family:=
Courier; font-size: 22px; color: rgb(0, 0, 0);"> a.pid AS bloc=
ked_pid,</span></p><p style=3D"line-height: normal; margin: 0px;"><span styl=
e=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> &nb=
sp; a.usename AS blocked_user,</span></p><p style=3D"line-height: normal; ma=
rgin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb=
(0, 0, 0);"> a.application_name AS blocked_app,</span></p><p st=
yle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courie=
r; font-size: 22px; color: rgb(0, 0, 0);"> a.client_addr AS blo=
cked_client,</span></p><p style=3D"line-height: normal; margin: 0px;"><span s=
tyle=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> &=
nbsp; now() - a.query_start AS blocked_runtime,</span></p><p style=3D"line-h=
eight: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size:=
22px; color: rgb(0, 0, 0);"> left(regexp_replace(a.query, '\s+=
', ' ', 'g'), 120) AS blocked_query,</span></p><p style=3D"line-height: norm=
al; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);"> unnest(pg_blocking_pids(a.pid)) AS blocking_=
pid</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"=
font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> FROM pg_=
stat_activity a</span></p><p style=3D"line-height: normal; margin: 0px;"><sp=
an style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&nb=
sp; WHERE a.state <> 'idle'</span></p><p style=3D"line-height: normal;=
margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: r=
gb(0, 0, 0);"> AND cardinality(pg_blocking_pids(a.pid)) > 0<=
/span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font=
-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">)</span></p><p styl=
e=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier;=
font-size: 22px; color: rgb(0, 0, 0);">SELECT</span></p><p style=3D"line-he=
ight: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 2=
2px; color: rgb(0, 0, 0);"> b.blocked_pid,</span></p><p style=3D"line-=
height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size=
: 22px; color: rgb(0, 0, 0);"> b.blocked_user,</span></p><p style=3D"l=
ine-height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-=
size: 22px; color: rgb(0, 0, 0);"> b.blocked_app,</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);"> b.blocked_client,</span></p><p st=
yle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courie=
r; font-size: 22px; color: rgb(0, 0, 0);"> date_trunc('second', b.bloc=
ked_runtime) AS blocked_runtime,</span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);"> b.blocked_query,</span></p><p style=3D"line-height: norm=
al; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);"> b.blocking_pid,</span></p><p style=3D"line-height: n=
ormal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; c=
olor: rgb(0, 0, 0);"> a.usename AS blocking_user,</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);"> a.application_name AS blocking_ap=
p,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"f=
ont-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> date_trun=
c('second', now() - a.query_start) AS blocking_runtime,</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);"> left(regexp_replace(a.query, '\s+=
', ' ', 'g'), 120) AS blocking_query</span></p><p style=3D"line-height: norm=
al; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);">FROM blocked b</span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);">JOIN pg_stat_activity a ON a.pid =3D b.blocking_pid</span></p><=
p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Co=
urier; font-size: 22px; color: rgb(0, 0, 0);">ORDER BY b.blocked_runtime DES=
C;</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-heig=
ht: 13.8px;"><span style=3D"font-family: "Times New Roman"; font-s=
ize: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: nor=
mal; margin: 0px 0px 14px;"><span style=3D"font-family: TimesNewRomanPS-Bold=
MT; font-size: 21px; color: rgb(0, 0, 0);"><b>1C. Heavy relations currently l=
ocked (what table is =E2=80=9Con fire=E2=80=9D)</b></span></p><p style=3D"li=
ne-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D=
"font-family: "Times New Roman"; font-size: 12px; color: rgb(0, 0,=
0);"><br></span></p><p style=3D"line-height: normal; margin: 0px;"><span st=
yle=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">SELECT</=
span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-=
family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> c.relname AS r=
elation,</span></p><p style=3D"line-height: normal; margin: 0px;"><span styl=
e=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> l.m=
ode,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> l.grant=
ed,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"=
font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> count(*)=
AS lock_count</span></p><p style=3D"line-height: normal; margin: 0px;"><spa=
n style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">FROM=
pg_locks l</span></p><p style=3D"line-height: normal; margin: 0px;"><span s=
tyle=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">JOIN pg=
_class c ON c.oid =3D l.relation</span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);">WHERE c.relkind IN ('r','p','i','m') -- table/partition/i=
ndex/materialized view</span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);">GROUP BY 1,2,3</span></p><p style=3D"line-height: normal; margin: 0px;">=
<span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">=
ORDER BY lock_count DESC, relation</span></p><p style=3D"line-height: normal=
; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color:=
rgb(0, 0, 0);">LIMIT 50;</span></p><p style=3D"line-height: normal; margin:=
0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Times N=
ew Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p sty=
le=3D"line-height: normal; margin: 0px; min-height: 13.8px;"><span style=3D"=
font-family: "Times New Roman"; font-size: 12px; color: rgb(128, 1=
28, 128);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 1=
2px; min-height: 13.8px;"><span style=3D"font-family: "Times New Roman&=
quot;; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"lin=
e-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"=
font-family: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0=
);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 14.9px;"=
><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 24px; color:=
rgb(0, 0, 0);"><b>2) =E2=80=9CWhat are the slowest queries?=E2=80=9D (requi=
res pg_stat_statements)</b></span></p><p style=3D"line-height: normal; margi=
n: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Time=
s New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p s=
tyle=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><spa=
n style=3D"font-family: "Times New Roman"; font-size: 12px; color:=
rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0=
px 12px;"><span style=3D"font-family: "Times New Roman"; font-size=
: 19px; color: rgb(0, 0, 0);">If you can enable one extension for performanc=
e work, make it this one.</span></p><p style=3D"line-height: normal; margin:=
0px 0px 12px;"><span style=3D"font-family: "Times New Roman"; fon=
t-size: 19px; color: rgb(0, 0, 0);">You=E2=80=99ll need shared_preload_libra=
ries =3D 'pg_stat_statements' and CREATE EXTENSION pg_stat_statements;</span=
></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8=
px;"><span style=3D"font-family: "Times New Roman"; font-size: 12p=
x; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; mar=
gin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Ti=
mes New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><=
p style=3D"line-height: normal; margin: 0px 0px 14px;"><span style=3D"font-f=
amily: TimesNewRomanPS-BoldMT; font-size: 21px; color: rgb(0, 0, 0);"><b>2A.=
Top total time (who costs you the most)</b></span></p><p style=3D"line-heig=
ht: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-f=
amily: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><=
br></span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"=
font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">SELECT</span></=
p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family:=
Courier; font-size: 22px; color: rgb(0, 0, 0);"> calls,</span></p><p s=
tyle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Couri=
er; font-size: 22px; color: rgb(0, 0, 0);"> round(total_exec_time::num=
eric, 1) AS total_ms,</span></p><p style=3D"line-height: normal; margin: 0px=
;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0)=
;"> round(mean_exec_time::numeric, 3) AS mean_ms,</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);"> rows,</span></p><p style=3D"line-=
height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size=
: 22px; color: rgb(0, 0, 0);"> round(100.0 * shared_blks_hit / NULLIF(=
shared_blks_hit + shared_blks_read,0), 2) AS cache_hit_pct,</span></p><p sty=
le=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier=
; font-size: 22px; color: rgb(0, 0, 0);"> left(regexp_replace(query, '=
\s+', ' ', 'g'), 160) AS query_160</span></p><p style=3D"line-height: normal=
; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color:=
rgb(0, 0, 0);">FROM pg_stat_statements</span></p><p style=3D"line-height: n=
ormal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; c=
olor: rgb(0, 0, 0);">ORDER BY total_exec_time DESC</span></p><p style=3D"lin=
e-height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-si=
ze: 22px; color: rgb(0, 0, 0);">LIMIT 20;</span></p><p style=3D"line-height:=
normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-fami=
ly: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br>=
</span></p><p style=3D"line-height: normal; margin: 0px 0px 14px;"><span sty=
le=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 21px; color: rgb(0, 0,=
0);"><b>2B. Top mean time (classic =E2=80=9Cone query ruins the day=E2=80=9D=
list)</b></span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; m=
in-height: 13.8px;"><span style=3D"font-family: "Times New Roman";=
font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-heig=
ht: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22=
px; color: rgb(0, 0, 0);">SELECT</span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);"> calls,</span></p><p style=3D"line-height: normal; margin=
: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0=
, 0);"> round(mean_exec_time::numeric, 3) AS mean_ms,</span></p><p sty=
le=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier=
; font-size: 22px; color: rgb(0, 0, 0);"> round(stddev_exec_time::nume=
ric, 3) AS stddev_ms,</span></p><p style=3D"line-height: normal; margin: 0px=
;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0)=
;"> round(max_exec_time::numeric, 1) AS max_ms,</span></p><p style=3D"=
line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; font=
-size: 22px; color: rgb(0, 0, 0);"> rows,</span></p><p style=3D"line-h=
eight: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size:=
22px; color: rgb(0, 0, 0);"> left(regexp_replace(query, '\s+', ' ', '=
g'), 160) AS query_160</span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);">FROM pg_stat_statements</span></p><p style=3D"line-height: normal; margi=
n: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0,=
0, 0);">WHERE calls >=3D 10</span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);">ORDER BY mean_exec_time DESC</span></p><p style=3D"line-height:=
normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px;=
color: rgb(0, 0, 0);">LIMIT 20;</span></p><p style=3D"line-height: normal; m=
argin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "=
Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p=
><p style=3D"line-height: normal; margin: 0px 0px 14px;"><span style=3D"font=
-family: TimesNewRomanPS-BoldMT; font-size: 21px; color: rgb(0, 0, 0);"><b>2=
C. IO-heavy queries (read storms)</b></span></p><p style=3D"line-height: nor=
mal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: &=
quot;Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></spa=
n></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-fam=
ily: Courier; font-size: 22px; color: rgb(0, 0, 0);">SELECT</span></p><p sty=
le=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier=
; font-size: 22px; color: rgb(0, 0, 0);"> calls,</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);"> (shared_blks_read + local_blks_re=
ad) AS blks_read,</span></p><p style=3D"line-height: normal; margin: 0px;"><=
span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&=
nbsp; (shared_blks_hit + local_blks_hit) AS blks_hit,</span></p>=
<p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: C=
ourier; font-size: 22px; color: rgb(0, 0, 0);"> round( (blk_read_time +=
blk_write_time)::numeric, 1) AS io_ms,</span></p><p style=3D"line-height: n=
ormal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; c=
olor: rgb(0, 0, 0);"> round(total_exec_time::numeric, 1) AS total_ms,<=
/span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font=
-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> left(regexp_=
replace(query, '\s+', ' ', 'g'), 160) AS query_160</span></p><p style=3D"lin=
e-height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-si=
ze: 22px; color: rgb(0, 0, 0);">FROM pg_stat_statements</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);">ORDER BY (shared_blks_read + local_blks_=
read) DESC</span></p><p style=3D"line-height: normal; margin: 0px;"><span st=
yle=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">LIMIT 20=
;</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-heigh=
t: 13.8px;"><span style=3D"font-family: "Times New Roman"; font-si=
ze: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: norm=
al; margin: 0px; min-height: 13.8px;"><span style=3D"font-family: "Time=
s New Roman"; font-size: 12px; color: rgb(128, 128, 128);"><br></span><=
/p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px=
;"><span style=3D"font-family: "Times New Roman"; font-size: 12px;=
color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margi=
n: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Time=
s New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p s=
tyle=3D"line-height: normal; margin: 0px 0px 14.9px;"><span style=3D"font-fa=
mily: TimesNewRomanPS-BoldMT; font-size: 24px; color: rgb(0, 0, 0);"><b>3) =E2=
=80=9CAre we index/scan healthy?=E2=80=9D (high-signal, low-noise)</b></span=
></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8=
px;"><span style=3D"font-family: "Times New Roman"; font-size: 12p=
x; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; mar=
gin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Ti=
mes New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><=
p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><=
span style=3D"font-family: "Times New Roman"; font-size: 12px; col=
or: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0=
px 0px 14px;"><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size:=
21px; color: rgb(0, 0, 0);"><b>3A. Tables with heavy sequential scans</b></=
span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 1=
3.8px;"><span style=3D"font-family: "Times New Roman"; font-size: 1=
2px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);">SELECT</span></p><p style=3D"line-height: normal; margin: 0px;"=
><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"=
> relname,</span></p><p style=3D"line-height: normal; margin: 0px;"><s=
pan style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&n=
bsp; seq_scan,</span></p><p style=3D"line-height: normal; margin: 0px;"><spa=
n style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&nbs=
p; seq_tup_read,</span></p><p style=3D"line-height: normal; margin: 0px;"><s=
pan style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&n=
bsp; idx_scan,</span></p><p style=3D"line-height: normal; margin: 0px;"><spa=
n style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&nbs=
p; n_live_tup,</span></p><p style=3D"line-height: normal; margin: 0px;"><spa=
n style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">&nbs=
p; round(100.0 * idx_scan / NULLIF(seq_scan + idx_scan,0), 2) AS idx_ratio_p=
ct</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"f=
ont-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">FROM pg_stat_use=
r_tables</span></p><p style=3D"line-height: normal; margin: 0px;"><span styl=
e=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">WHERE (seq=
_scan + idx_scan) > 0</span></p><p style=3D"line-height: normal; margin: 0=
px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);">ORDER BY seq_tup_read DESC</span></p><p style=3D"line-height: normal; ma=
rgin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb=
(0, 0, 0);">LIMIT 25;</span></p><p style=3D"line-height: normal; margin: 0px=
0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Times New R=
oman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D=
"line-height: normal; margin: 0px 0px 14px;"><span style=3D"font-family: Tim=
esNewRomanPS-BoldMT; font-size: 21px; color: rgb(0, 0, 0);"><b>3B. Index hit=
ratio (quick =E2=80=9Ccache health=E2=80=9D indicator)</b></span></p><p sty=
le=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span s=
tyle=3D"font-family: "Times New Roman"; font-size: 12px; color: rg=
b(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px;"><=
span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">S=
ELECT</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> datname=
,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"fo=
nt-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> round(100.=
0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct,</span><=
/p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family=
: Courier; font-size: 22px; color: rgb(0, 0, 0);"> blks_read,</span></=
p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family:=
Courier; font-size: 22px; color: rgb(0, 0, 0);"> blks_hit</span></p><=
p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Co=
urier; font-size: 22px; color: rgb(0, 0, 0);">FROM pg_stat_database</span></=
p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font-family:=
Courier; font-size: 22px; color: rgb(0, 0, 0);">ORDER BY cache_hit_pct ASC N=
ULLS LAST;</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; m=
in-height: 13.8px;"><span style=3D"font-family: "Times New Roman";=
font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-heig=
ht: normal; margin: 0px; min-height: 13.8px;"><span style=3D"font-family: &q=
uot;Times New Roman"; font-size: 12px; color: rgb(128, 128, 128);"><br>=
</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height=
: 13.8px;"><span style=3D"font-family: "Times New Roman"; font-siz=
e: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: norma=
l; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: &q=
uot;Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span=
></p><p style=3D"line-height: normal; margin: 0px 0px 14.9px;"><span style=3D=
"font-family: TimesNewRomanPS-BoldMT; font-size: 24px; color: rgb(0, 0, 0);"=
><b>4) =E2=80=9CAutovacuum / bloat / dead tuples=E2=80=9D (the silent killer=
)</b></span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-h=
eight: 13.8px;"><span style=3D"font-family: "Times New Roman"; fon=
t-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: n=
ormal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family=
: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></=
span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 1=
3.8px;"><span style=3D"font-family: "Times New Roman"; font-size: 1=
2px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; m=
argin: 0px 0px 14px;"><span style=3D"font-family: TimesNewRomanPS-BoldMT; fo=
nt-size: 21px; color: rgb(0, 0, 0);"><b>4A. Dead tuples & vacuum stats (=
which tables need love)</b></span></p><p style=3D"line-height: normal; margi=
n: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Time=
s New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p s=
tyle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Couri=
er; font-size: 22px; color: rgb(0, 0, 0);">SELECT</span></p><p style=3D"line=
-height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-siz=
e: 22px; color: rgb(0, 0, 0);"> relname,</span></p><p style=3D"line-he=
ight: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 2=
2px; color: rgb(0, 0, 0);"> n_live_tup,</span></p><p style=3D"line-hei=
ght: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 2=
2px; color: rgb(0, 0, 0);"> n_dead_tup,</span></p><p style=3D"line-hei=
ght: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 2=
2px; color: rgb(0, 0, 0);"> round(100.0 * n_dead_tup / NULLIF(n_live_t=
up + n_dead_tup,0), 2) AS dead_pct,</span></p><p style=3D"line-height: norma=
l; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color=
: rgb(0, 0, 0);"> last_vacuum,</span></p><p style=3D"line-height: norm=
al; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);"> last_autovacuum,</span></p><p style=3D"line-height:=
normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px;=
color: rgb(0, 0, 0);"> last_analyze,</span></p><p style=3D"line-heigh=
t: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22p=
x; color: rgb(0, 0, 0);"> last_autoanalyze</span></p><p style=3D"line-=
height: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size=
: 22px; color: rgb(0, 0, 0);">FROM pg_stat_user_tables</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);">ORDER BY n_dead_tup DESC</span></p><p st=
yle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courie=
r; font-size: 22px; color: rgb(0, 0, 0);">LIMIT 25;</span></p><p style=3D"li=
ne-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D=
"font-family: "Times New Roman"; font-size: 12px; color: rgb(0, 0,=
0);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 14px;"=
><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 21px; color:=
rgb(0, 0, 0);"><b>4B. Autovacuum activity =E2=80=9Cright now=E2=80=9D</b></=
span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 1=
3.8px;"><span style=3D"font-family: "Times New Roman"; font-size: 1=
2px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; m=
argin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: rg=
b(0, 0, 0);">SELECT</span></p><p style=3D"line-height: normal; margin: 0px;"=
><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"=
> pid,</span></p><p style=3D"line-height: normal; margin: 0px;"><span s=
tyle=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> d=
atname,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=
=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> usen=
ame,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> state,<=
/span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D"font=
-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> wait_event_t=
ype,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> wait_ev=
ent,</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> date_tr=
unc('second', now() - query_start) AS runtime,</span></p><p style=3D"line-he=
ight: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 2=
2px; color: rgb(0, 0, 0);"> left(regexp_replace(query, '\s+', ' ', 'g'=
), 140) AS query_140</span></p><p style=3D"line-height: normal; margin: 0px;=
"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);=
">FROM pg_stat_activity</span></p><p style=3D"line-height: normal; margin: 0=
px;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);">WHERE query ILIKE 'autovacuum:%'</span></p><p style=3D"line-height: norm=
al; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);">ORDER BY (now() - query_start) DESC;</span></p><p style=3D=
"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=
=3D"font-family: "Times New Roman"; font-size: 12px; color: rgb(0,=
0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px; min-he=
ight: 13.8px;"><span style=3D"font-family: "Times New Roman"; font=
-size: 12px; color: rgb(128, 128, 128);"><br></span></p><p style=3D"line-hei=
ght: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-=
family: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);">=
<br></span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-he=
ight: 13.8px;"><span style=3D"font-family: "Times New Roman"; font=
-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: n=
ormal; margin: 0px 0px 14.9px;"><span style=3D"font-family: TimesNewRomanPS-=
BoldMT; font-size: 24px; color: rgb(0, 0, 0);"><b>5) =E2=80=9COne-liner repo=
rt=E2=80=9D you can paste into Slack (executive snapshot)</b></span></p><p s=
tyle=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><spa=
n style=3D"font-family: "Times New Roman"; font-size: 12px; color:=
rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px;=
"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);=
">SELECT</span></p><p style=3D"line-height: normal; margin: 0px;"><span styl=
e=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> now=
() AS ts,</span></p><p style=3D"line-height: normal; margin: 0px;"><span sty=
le=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> (S=
ELECT count(*) FROM pg_stat_activity WHERE state <> 'idle') AS active_=
sessions,</span></p><p style=3D"line-height: normal; margin: 0px;"><span sty=
le=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);"> (S=
ELECT count(*) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL AND s=
tate <> 'idle') AS waiting_sessions,</span></p><p style=3D"line-height=
: normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px=
; color: rgb(0, 0, 0);"> (SELECT count(*) FROM pg_stat_activity WHERE c=
ardinality(pg_blocking_pids(pid)) > 0) AS blocked_sessions,</span></p><p s=
tyle=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Couri=
er; font-size: 22px; color: rgb(0, 0, 0);"> (SELECT round(100.0 * blks=
_hit / NULLIF(blks_hit + blks_read,0), 2)</span></p><p style=3D"line-height:=
normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px;=
color: rgb(0, 0, 0);"> FROM pg_stat_database WHERE datname =3D c=
urrent_database()) AS cache_hit_pct;</span></p><p style=3D"line-height: norm=
al; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: &=
quot;Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></spa=
n></p><p style=3D"line-height: normal; margin: 0px; min-height: 13.8px;"><sp=
an style=3D"font-family: "Times New Roman"; font-size: 12px; color=
: rgb(128, 128, 128);"><br></span></p><p style=3D"line-height: normal; margi=
n: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Time=
s New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p s=
tyle=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><spa=
n style=3D"font-family: "Times New Roman"; font-size: 12px; color:=
rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0=
px 14.9px;"><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 2=
4px; color: rgb(0, 0, 0);"><b>How to turn these into a =E2=80=9Cnice output=E2=
=80=9D script (repeatable + pretty)</b></span></p><p style=3D"line-height: n=
ormal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family=
: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></=
span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 1=
3.8px;"><span style=3D"font-family: "Times New Roman"; font-size: 1=
2px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; m=
argin: 0px 0px 12px;"><span style=3D"font-family: "Times New Roman"=
;; font-size: 19px; color: rgb(0, 0, 0);">If you want one script that prints=
sections with headers, timestamps, and consistent columns, the cleanest rou=
te is:</span></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min-=
height: 13.8px;"><span style=3D"font-family: "Times New Roman"; fo=
nt-size: 12px; color: rgb(0, 0, 0);"><br></span></p><ul style=3D"list-style-=
type: disc;"><li style=3D"font-family: "Times New Roman"; font-siz=
e: 19px; color: rgb(0, 0, 0); line-height: normal; margin: 0px 0px 12px;">ps=
ql + \echo headers + \x auto (fast, minimal dependencies)</li><li style=3D"f=
ont-family: "Times New Roman"; font-size: 19px; color: rgb(0, 0, 0=
); line-height: normal; margin: 0px 0px 12px;">Or a small Bash/Python wrappe=
r that runs each query, renders tables, and optionally exports CSV/JSON.</li=
></ul><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.=
8px;"><span style=3D"font-family: "Times New Roman"; font-size: 12=
px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; ma=
rgin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "T=
imes New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p>=
<p style=3D"line-height: normal; margin: 0px 0px 12px;"><span style=3D"font-=
family: "Times New Roman"; font-size: 19px; color: rgb(0, 0, 0);">=
Here=E2=80=99s a simple pattern for psql:</span></p><p style=3D"line-height:=
normal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px;=
color: rgb(0, 0, 0);">\timing on</span></p><p style=3D"line-height: normal;=
margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; color: r=
gb(0, 0, 0);">\x auto</span></p><p style=3D"line-height: normal; margin: 0px=
;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0)=
;">\pset pager off</span></p><p style=3D"line-height: normal; margin: 0px; m=
in-height: 22px;"><span style=3D"font-family: Courier; font-size: 22px; colo=
r: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; margin: 0p=
x;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0=
);">\echo '=3D=3D=3D ACTIVE SESSIONS (top 30) =3D=3D=3D'</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);">-- paste query 1A</span></p><p style=3D"=
line-height: normal; margin: 0px; min-height: 22px;"><span style=3D"font-fam=
ily: Courier; font-size: 22px; color: rgb(0, 0, 0);"><br></span></p><p style=
=3D"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; f=
ont-size: 22px; color: rgb(0, 0, 0);">\echo '=3D=3D=3D BLOCKING CHAINS =3D=3D=
=3D'</span></p><p style=3D"line-height: normal; margin: 0px;"><span style=3D=
"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">-- paste query=
1B</span></p><p style=3D"line-height: normal; margin: 0px; min-height: 22px=
;"><span style=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0)=
;"><br></span></p><p style=3D"line-height: normal; margin: 0px;"><span style=
=3D"font-family: Courier; font-size: 22px; color: rgb(0, 0, 0);">\echo '=3D=3D=
=3D TOP QUERIES (TOTAL TIME) =3D=3D=3D'</span></p><p style=3D"line-height: n=
ormal; margin: 0px;"><span style=3D"font-family: Courier; font-size: 22px; c=
olor: rgb(0, 0, 0);">-- paste query 2A</span></p><p style=3D"line-height: no=
rmal; margin: 0px 0px 12px;"><span style=3D"font-family: "Times New Rom=
an"; font-size: 19px; color: rgb(0, 0, 0);">Run it:</span></p><p style=3D=
"line-height: normal; margin: 0px;"><span style=3D"font-family: Courier; fon=
t-size: 22px; color: rgb(0, 0, 0);">psql "postgresql://user:pass@host:5432/d=
bname" -f perf_report.sql</span></p><p style=3D"line-height: normal; margin:=
0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Times N=
ew Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p sty=
le=3D"line-height: normal; margin: 0px; min-height: 13.8px;"><span style=3D"=
font-family: "Times New Roman"; font-size: 12px; color: rgb(128, 1=
28, 128);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 1=
2px; min-height: 13.8px;"><span style=3D"font-family: "Times New Roman&=
quot;; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"lin=
e-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"=
font-family: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0=
);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 14.9px;"=
><span style=3D"font-family: TimesNewRomanPS-BoldMT; font-size: 24px; color:=
rgb(0, 0, 0);"><b>What I=E2=80=99d enable day-1 on any new project (minimal=
, high ROI)</b></span></p><p style=3D"line-height: normal; margin: 0px 0px 1=
2px; min-height: 13.8px;"><span style=3D"font-family: "Times New Roman&=
quot;; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"lin=
e-height: normal; margin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"=
font-family: "Times New Roman"; font-size: 12px; color: rgb(0, 0, 0=
);"><br></span></p><ol start=3D"1" style=3D"list-style-type: decimal;"><li s=
tyle=3D"font-family: "Times New Roman"; font-size: 19px; color: rg=
b(0, 0, 0); line-height: normal; margin: 0px 0px 12px;">pg_stat_statements (=
query-level truth)</li><li style=3D"font-family: "Times New Roman"=
; font-size: 19px; color: rgb(0, 0, 0); line-height: normal; margin: 0px 0px=
12px;">auto_explain (optional: catches slow queries at runtime, but use car=
efully)</li><li style=3D"font-family: "Times New Roman"; font-size=
: 19px; color: rgb(0, 0, 0); line-height: normal; margin: 0px 0px 12px;">log=
_min_duration_statement (or sampling) for forensic timeline correlation</li>=
</ol><p style=3D"line-height: normal; margin: 0px 0px 12px; min-height: 13.8=
px;"><span style=3D"font-family: "Times New Roman"; font-size: 12p=
x; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line-height: normal; mar=
gin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Ti=
mes New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><=
p style=3D"line-height: normal; margin: 0px 0px 12px;"><span style=3D"font-f=
amily: "Times New Roman"; font-size: 19px; color: rgb(0, 0, 0);">T=
hese are standard Postgres instrumentation options discussed in official doc=
s for pg_stat_statements and statistics views (pg_stat_activity, pg_locks, p=
g_stat_database, etc.).</span></p><p style=3D"line-height: normal; margin: 0=
px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "Times Ne=
w Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p styl=
e=3D"line-height: normal; margin: 0px; min-height: 13.8px;"><span style=3D"f=
ont-family: "Times New Roman"; font-size: 12px; color: rgb(128, 12=
8, 128);"><br></span></p><p style=3D"line-height: normal; margin: 0px 0px 12=
px; min-height: 13.8px;"><span style=3D"font-family: "Times New Roman&q=
uot;; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p><p style=3D"line=
-height: normal; margin: 0px 0px 12px;"><span style=3D"font-family: "Ti=
mes New Roman"; font-size: 19px; color: rgb(0, 0, 0);">If you tell me (=
a) Postgres version, (b) managed service vs self-hosted, and (c) whether you=
can enable pg_stat_statements, I=E2=80=99ll bundle this into a single =E2=80=
=9CPerf Pack=E2=80=9D file with:</span></p><p style=3D"line-height: normal; m=
argin: 0px 0px 12px; min-height: 13.8px;"><span style=3D"font-family: "=
Times New Roman"; font-size: 12px; color: rgb(0, 0, 0);"><br></span></p=
><ul style=3D"list-style-type: disc;"><li style=3D"font-family: "Times N=
ew Roman"; font-size: 19px; color: rgb(0, 0, 0); line-height: normal; m=
argin: 0px 0px 12px;">perf_report.sql (human-readable)</li><li style=3D"font=
-family: "Times New Roman"; font-size: 19px; color: rgb(0, 0, 0); l=
ine-height: normal; margin: 0px 0px 12px;">perf_report.csv export mode</li><=
li style=3D"font-family: "Times New Roman"; font-size: 19px; color=
: rgb(0, 0, 0); line-height: normal; margin: 0px 0px 12px;">optional =E2=80=9C=
baseline vs now=E2=80=9D delta mode (best for stakeholder questions like =E2=
=80=9Cdid it get worse after deploy?=E2=80=9D)</li></ul><p style=3D"line-hei=
ght: normal; margin: 0px 0px 12px; min-height: 13.8px;"><font face=3D"Times N=
ew Roman" size=3D"3">Lazaro Sanchez CEO at Cyberellum Technologies & Lab=
oratory</font></p><p style=3D"line-height: normal; margin: 0px 0px 12px; min=
-height: 13.8px;"><font face=3D"Times New Roman" size=3D"3">[email protected]=
echnology </font></p><p style=3D"line-height: normal; margin: 0px 0px 1=
2px; min-height: 13.8px;"><font face=3D"Times New Roman" size=3D"3"><br></fo=
nt></p><div style=3D"color: rgb(0, 0, 0);" dir=3D"ltr"><br></div><div dir=3D=
"ltr"><br></div><div id=3D"ms-outlook-mobile-body-separator-line" data-apply=
defaultfontstyles=3D"true" style=3D"font-family: Aptos, Aptos_MSFontService,=
-apple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt;" dir=3D=
"ltr"><div dir=3D"ltr" style=3D"font-family: Aptos, Aptos_MSFontService, -ap=
ple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt;"><br></di=
v></div><div style=3D"font-family: Aptos, Aptos_MSFontService, -apple-system=
, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt;" id=3D"ms-outlook-m=
obile-signature"><span style=3D"font-family: Aptos, Aptos_MSFontService, -ap=
ple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt;">Get <a h=
ref=3D"https://aka.ms/o0ukef">Outlook for iOS</a></span></div><div id=3D"mai=
l-editor-reference-message-container"><hr style=3D"display: inline-block; wi=
dth: 98%;"><div id=3D"divRplyFwdMsg" dir=3D"ltr"><span style=3D"font-family:=
Calibri, sans-serif;"><b>From:</b> P=C3=A4r Mattsson <par.x.mattsso=
[email protected]><br><b>Sent:</b> Monday, December 22, 2025 10:35 AM<br><=
b>To:</b> Pgsql-admin <[email protected]><br><b>Su=
bject:</b> Performance issue!</span><div style=3D"font-family: Calibri,=
sans-serif;"> </div></div><div dir=3D"ltr" style=3D"font-family: Aptos=
, Aptos_MSFontService, -apple-system, Roboto, Arial, Helvetica, sans-serif; f=
ont-size: 12pt; color: rgb(0, 0, 0);">
Hi!</div><div dir=3D"ltr" style=3D"font-family: Aptos, Aptos_MSFontService, -=
apple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt; color: r=
gb(0, 0, 0);">
Anyone who have performance scripts with nice output to share.</div><div dir=
=3D"ltr" style=3D"font-family: Aptos, Aptos_MSFontService, -apple-system, Ro=
boto, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><=
br>
</div><div dir=3D"ltr" style=3D"font-family: Aptos, Aptos_MSFontService, -ap=
ple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt; color: rg=
b(0, 0, 0);">
I am in a new project where we got lots of questions about performance on th=
e database!</div><div dir=3D"ltr" style=3D"font-family: Aptos, Aptos_MSFontS=
ervice, -apple-system, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt=
; color: rgb(0, 0, 0);"><br>
</div><div id=3D"ms-outlook-mobile-body-separator-line" dir=3D"ltr" style=3D=
"font-family:Aptos,Aptos_MSFontService,-apple-system,Roboto,Arial,Helvetica,=
sans-serif; font-size:12pt"><div dir=3D"ltr" style=3D"font-family: Aptos, Ap=
tos_MSFontService, -apple-system, Roboto, Arial, Helvetica, sans-serif; font=
-size: 12pt;"><br>
</div></div><div id=3D"ms-outlook-mobile-signature" style=3D"font-family:Apt=
os,Aptos_MSFontService,-apple-system,Roboto,Arial,Helvetica,sans-serif; font=
-size:12pt"><div style=3D"font-family: Aptos, Aptos_MSFontService, -apple-sy=
stem, Roboto, Arial, Helvetica, sans-serif; font-size: 12pt;">
Mvh P=C3=A4r</div></div></div></div></blockquote></div></body></html>=
--Apple-Mail-9E4870C3-42C8-4BB8-9532-EE48E258CC61--
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Performance issue!
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox