agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedPerformance issue
5+ messages / 3 participants
[nested] [flat]
* Performance issue
@ 2005-02-05 09:16 Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>
0 siblings, 1 reply; 5+ messages in thread
From: Pascal Van Puymbroeck @ 2005-02-05 09:16 UTC (permalink / raw)
To: pgsql-admin
Hi folks,
I would like to solve a strange behaviour I encountered using Postgresql
8.0.1 on windows. Connecting to the database is OK, but when running a
query to the database, its terribly slow. The fact is that I need the
database to be running on the same machine, my application is. So I
connect to either localhost, 127.0.0.1 or 192.168.0.1. Strangly enough
I placed my application on my portable and connected from there to my pc
and now the queries are amazingly fast.
Is this something I can fix by some special setting or is it inevitably,
that connecting locally is punished by a performance slowdown ?
Thanks
Pascal Van Puymbroeck
ZENON productions bvba
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue
@ 2005-02-05 16:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-02-05 16:53 UTC (permalink / raw)
To: Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>; +Cc: pgsql-admin
Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be> writes:
> I would like to solve a strange behaviour I encountered using Postgresql
> 8.0.1 on windows. Connecting to the database is OK, but when running a
> query to the database, its terribly slow. The fact is that I need the
> database to be running on the same machine, my application is.
There was a recent thread that came to the conclusion that loopback TCP
connections are remarkably slow on Windows unless you've installed the
correct add-on package --- something not tremendously obvious, I think
it was a "QOS" patch or maybe something to do with packet filtering.
Check the archives of the various pgsql lists. I forget which list but
it was just a couple weeks ago.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue
@ 2005-02-05 21:51 Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>
0 siblings, 0 replies; 5+ messages in thread
From: Pascal Van Puymbroeck @ 2005-02-05 21:51 UTC (permalink / raw)
To: pgsql-admin
OK, I found the thread you mentioned and installed the Qos Service on my
win2K netwerk, but it gave no effect. So it must be something else in
this case.
Pascal Van Puymbroeck
ZENON productions bvba
^ permalink raw reply [nested|flat] 5+ messages in thread
* Performance issue!
@ 2025-12-22 15:34 Pär Mattsson <par.x.mattsson@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pär Mattsson @ 2025-12-22 15:34 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi!
Anyone who have performance scripts with nice output to share.
I am in a new project where we got lots of questions about performance on the database!
Mvh Pär
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue!
@ 2025-12-22 16:38 Pär Mattsson <par.x.mattsson@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Pär Mattsson @ 2025-12-22 16:38 UTC (permalink / raw)
To: ceo@goodfellas.agency; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
--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 <ceo@goodfellas.agency>:<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">ceo@cyberellum.t=
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=
n@gmail.com><br><b>Sent:</b> Monday, December 22, 2025 10:35 AM<br><=
b>To:</b> Pgsql-admin <pgsql-admin@lists.postgresql.org><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--
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-12-22 16:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-05 09:16 Performance issue Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>
2005-02-05 16:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-05 21:51 Re: Performance issue Pascal Van Puymbroeck <pascal.vanpuymbroeck@zenonproductions.be>
2025-12-22 15:34 Performance issue! Pär Mattsson <par.x.mattsson@gmail.com>
2025-12-22 16:38 Re: Performance issue! Pär Mattsson <par.x.mattsson@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox