Re: huge shared_blocks_hit one select but manually run very fast

Out of interest. Why is a UUID being stored as a varchar(64) and not as a
uuid type ?
Ny motivation is to makes tables much smaller and much faster to lookup a
key if the key is of the correct data-type.

On Sun, 22 Dec 2024 at 03:38, James Pang <jamespang886(at)gmail(dot)com> wrote:

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