huge shared_blocks_hit one select but manually run very fast
James Pang <jamespang886@gmail.com>
From: James Pang <jamespang886@gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: 2024-12-21T11:50:40Z
Lists: pgsql-performance
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