Re: huge shared_blocks_hit one select but manually run very fast

Depends on a lot of thongs...Visibility map sounds like it's impacted here.
Are your inserts towards the index (like a monotonically increasing serial
id)  or scattered around the index values ?   How big is the table  index
and shared buffers ?   An example would really help

On Sat, 21 Dec 2024, 11:51 James Pang, <jamespang886@gmail.com> wrote:

> 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
>