Hi,
We found one simple query manually run very fast(finished in several
milliseconds), but there are 2 sessions within long transaction to run same sql
with same bind variables took tens of seconds.
Manually run this sql only show <100 shared_blks_hit and very small reads, but
for these 2 long running SQL from pg_stat_statements, it show huge
shared_blks_hits/reads, and some shared_blks_dirtied/written for this query
too. It's a very hot table and a lot of sessions update/delete/insert on this
table. It looks like the query have scan huge blocks for MVCC ? any
suggestions to tune this case ? any idea why shared_blks_dirtied/written for
this query?
userid | 17443
dbid| 16384
toplevel| t
queryid | 6334282481325858045
query | SELECT , FROM test.x
WHERE ( ( id1 = $1 ) ) AND ( ( id2 = $2 ) ) AND ( ( id3 = $3 ) )
plans | 0
total_plan_time | 0
min_plan_time | 0
max_plan_time | 0
mean_plan_time | 0
stddev_plan_time| 0
calls | 2142
total_exec_time | 66396685.619224936
min_exec_time | 7221.611607
max_exec_time | 391486.974656
mean_exec_time | 30997.51896322356
stddev_exec_time| 31073.83250436726
rows| 153
shared_blks_hit | 7133350479
shared_blks_read| 2783620426
shared_blks_dirtied | 1853702
shared_blks_written | 2329513
local_blks_hit | 0
local_blks_read | 0
local_blks_dirtied | 0
local_blks_written | 0
temp_blks_read | 0
temp_blks_written | 0
blk_read_time | 0
blk_write_time | 0
wal_records | 237750
wal_fpi | 207790
wal_bytes | 442879812
pid | state | xact_start | query_start
| wait_event_type | wait_event | backend_xid | backend
_xmin
-++---+---+-++-+
--
3671416 | active | 2023-11-16 06:38:16.802127+00 | 2023-11-16
08:08:15.739509+00 | || 159763259 |159763259
3671407 | active | 2023-11-16 06:38:16.807064+00 | 2023-11-16
08:08:17.195405+00 | || 159764118 |159763259
--table size
relpages | reltuples
--+---
3146219 | 1.9892568e+08
--index size
relpages | reltuples
--+---
1581759 | 1.9892568e+08
Thanks,
James
