Re: huge shared_blocks_hit one select but manually run very fast

Yes, we have primary key and another index that include bigint and uuid
that stored in database as varchar(64), many sessions inserting ...
values and at the same time, many sessions running the select query.
table size 13GB, primary is 6GB, the other index is 13GB too, the select
query use the 13GB index only scan.
in addition to this table and query, during the workload, we have other
two hot tables with similar bigint and uuid composite index, inserting by
many sessions and select in many sessions too, but the table size is
smaller, table 3790MB, index 1445MB.
when the workload started, database server cpu got increased very fast
and in tens of seconds, cpu is almost 100% used, and then
buffermapping LWLock started for these selects , even the inserts started
see buffermapping contention too.
attached please find top 1 table and sql details, another two tables and
it's query are similar.

Thanks,

James

David Mullineux <dmullx(at)gmail(dot)com> 於 2024年12月22日週日 上午12:41寫道：

> 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(at)gmail(dot)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
>>
>