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 <jamespang...@gmail.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 <dmu...@gmail.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, <jamespang...@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 >>> >>
