|
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 xxxx, xxxx FROM test.xxxxx 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 |
