MIME-Version: 1.0
From: James Pang <jamespang886@gmail.com>
Date: Sat, 21 Dec 2024 19:50:40 +0800
Message-ID: 
 <CAHgTRff_1mTsU-QSaDBY5k9+D-i9sV0aQeRY9ShffV=6b7jOWQ@mail.gmail.com>
Subject: huge shared_blocks_hit one select but manually run very fast
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000015f4760629c660aa"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRff_1mTsU-QSaDBY5k9%2BD-i9sV0aQeRY9ShffV%3D6b7jOWQ%40mail.gmail.com>
Precedence: bulk

--00000000000015f4760629c660aa
Content-Type: text/plain; charset="UTF-8"

Hi,
   we have a simple select .... from table where ... (that mache the index)
, table has 80million rows.  when many application sessions run the query
and at the same time some other sessions doing insert into ... this table.
from pg_stat_statements, shared_blks_hit show 31652 / per call.   we see
very high cpu almost 100% cpu during application workload test, and high
LWLock BufferMapping waiting for these querys.  But manually run the sql
show only 2148 shared_blks_hit/ per call.  this is a simple sql, from
pg_profile we did see it use same index scan as manually running.  What
could be possible reason leading so big difference with shared_blks_hit ?
 PGv14.8

Thanks,

James

--00000000000015f4760629c660aa
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,=C2=A0<div>=C2=A0 =C2=A0we have a simple select .... fr=
om table where ... (that mache the index) , table has 80million rows.=C2=A0=
 when many application sessions run the query and at the same time some oth=
er sessions doing insert into ... this table. from pg_stat_statements, shar=
ed_blks_hit show 31652 / per call.=C2=A0 =C2=A0we see very high cpu almost =
100% cpu during application workload test, and high LWLock=C2=A0BufferMappi=
ng waiting for these querys.=C2=A0 But manually=C2=A0run the sql show only =
2148 shared_blks_hit/ per call.=C2=A0 this is a simple sql, from pg_profile=
 we did see it use same index scan as manually=C2=A0running.=C2=A0 What cou=
ld be possible reason leading so big difference with shared_blks_hit ?=C2=
=A0=C2=A0</div><div>=C2=A0PGv14.8=C2=A0</div><div><br></div><div>Thanks,</d=
iv><div><br></div><div>James</div></div>

--00000000000015f4760629c660aa--