Sorry ,A wrong version of debug pcnt_visibletuples ，kindly please check the v3 attachment
On Fri, Mar 7, 2025 at 5:37 PM wenhui qiu <qiuwenhu...@gmail.com> wrote: > Hi > The more accurate data I've found is tabentry->live_tuples; provides > the second version > > #Here's a simple test I did > > test=# select count(*) from join1; > count > --------- > 2289001 > (1 row) > > > test=# update join1 set name=md5(now()::text) where id<1000000; > UPDATE 1938700 > test=# select 1938700/2289001; > ?column? > ---------- > 0 > (1 row) > > test=# select 1938700/2289001::float; > ?column? > -------------------- > 0.8469633696097119 > (1 row) > > test=# > > > > test=# select count(*) from join1; > count > --------- > 2289001 > (1 row) > test=# update join1 set name=md5(now()::text) where id<=80000; > UPDATE 159901 > test=# select 159901/2289001::float; > ?column? > --------------------- > 0.06985623859491542 > (1 row) > > > test=# select * from pg_stat_all_tables where relname='join1'; > -[ RECORD 1 ]----------+------------------------------ > relid | 16385 > schemaname | public > relname | join1 > seq_scan | 17 > last_seq_scan | 2025-03-07 15:34:02.793659+08 > seq_tup_read | 14994306 > idx_scan | 7 > last_idx_scan | 2025-03-07 15:34:23.404788+08 > idx_tup_fetch | 500281 > n_tup_ins | 2289001 > n_tup_upd | 2268604 > n_tup_del | 0 > n_tup_hot_upd | 399 > n_tup_newpage_upd | 2268205 > n_live_tup | 2286701 > n_dead_tup | 159901 > n_mod_since_analyze | 159901 > n_ins_since_vacuum | 0 > last_vacuum | 2025-03-06 18:18:11.318419+08 > last_autovacuum | 2025-03-07 15:25:53.055576+08 > last_analyze | 2025-03-06 18:18:11.424253+08 > last_autoanalyze | 2025-03-07 15:25:53.456656+08 > vacuum_count | 3 > autovacuum_count | 3 > analyze_count | 2 > autoanalyze_count | 4 > total_vacuum_time | 205 > total_autovacuum_time | 2535 > total_analyze_time | 203 > total_autoanalyze_time | 1398 > > test=# > test=# update join1 set name=md5(now()::text) where id<=80000; > UPDATE 159901 > > > test=# \x > Expanded display is on. > test=# select (n_live_tup)/(n_live_tup+n_dead_tup)::float from > pg_stat_all_tables where relname='join1'; > -[ RECORD 1 ]---------------- > ?column? | 0.8774142777358045 > > test=# select * from pg_stat_all_tables where relname='join1'; > -[ RECORD 1 ]----------+------------------------------ > relid | 16385 > schemaname | public > relname | join1 > seq_scan | 17 > last_seq_scan | 2025-03-07 15:34:02.793659+08 > seq_tup_read | 14994306 > idx_scan | 8 > last_idx_scan | 2025-03-07 15:46:38.331795+08 > idx_tup_fetch | 660182 > n_tup_ins | 2289001 > n_tup_upd | 2428505 > n_tup_del | 0 > n_tup_hot_upd | 424 > n_tup_newpage_upd | 2428081 > n_live_tup | 2289001 > n_dead_tup | 319802 > n_mod_since_analyze | 0 > n_ins_since_vacuum | 0 > last_vacuum | 2025-03-06 18:18:11.318419+08 > last_autovacuum | 2025-03-07 15:25:53.055576+08 > last_analyze | 2025-03-06 18:18:11.424253+08 > last_autoanalyze | 2025-03-07 15:47:35.950932+08 > vacuum_count | 3 > autovacuum_count | 3 > analyze_count | 2 > autoanalyze_count | 5 > total_vacuum_time | 205 > total_autovacuum_time | 2535 > total_analyze_time | 203 > total_autoanalyze_time | 1770 > > test=# > tail -n 1000 postgresql-Fri_17.csv |grep join1 > 2025-03-07 17:30:12.782 +08,,,755739,,67cabca4.b881b,3,,2025-03-07 > 17:30:12 +08,2017/2,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: > 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: > 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0 > 2025-03-07 17:31:12.803 +08,,,756028,,67cabce0.b893c,3,,2025-03-07 > 17:31:12 +08,2003/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: > 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: > 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0 > 2025-03-07 17:32:12.822 +08,,,756405,,67cabd1c.b8ab5,3,,2025-03-07 > 17:32:12 +08,2006/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: > 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: > 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0 > 2025-03-07 17:33:12.842 +08,,,757026,,67cabd58.b8d22,3,,2025-03-07 > 17:33:12 +08,2009/4,0,DEBUG,00000,"vacthresh: 457850.218750,anlthresh: > 228950.109375, the join1 has 2289001.000000 reltuples, pcnt_unfrozen: > 1.000000, pcnt_visibletuples: 0.877414 ",,,,,,,,,"","autovacuum worker",,0 > > On Fri, Mar 7, 2025 at 2:22 PM wenhui qiu <qiuwenhu...@gmail.com> wrote: > >> HI Nathan Bossart Melanie Plageman >> >> Firstly, congratulations on the submission of this path: >> https://commitfest.postgresql.org/patch/5320/ >> >> vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples; >> anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples; >> vacinsthresh = (float4) vac_ins_base_thresh + vac_ins_scale_factor * >> reltuples; >> These three calculations have already been optimised for two of them, and >> with this patch, we have the key data pcnt_unfrozen, I think we can also >> consider applying it to the vacthresh and anlthresh calculations, and I've >> added a new pcnt_unrelallvisible parameter with reference to pcnt_unfrozen, >> so I'm not sure if it's a good idea for me to use it. I'd like to hear your >> opinions on this. >> #Here's a simple test I did >> test=# select count(*) from join1; >> count >> --------- >> 2289001 >> (1 row) >> >> test=# update join1 set name=md5(now()::text) where id<=20000; >> UPDATE 70001 >> test=# >> >> 2025-03-07 14:03:33.968 +08,,,607191,,67ca8c35.943d7,2,,2025-03-07 >> 14:03:33 +08,2005/2,0,DEBUG,00000,"vacthresh: 222674.750000,anlthresh: >> 11371.118164, the j >> oin1 has 2291275.000000 reltuples, pcnt_unfrozen: 0.485810, >> pcnt_unrelallvisible: 0.049410 ",,,,,,,,,"","autovacuum worker",,0 >> >> >
Optimising-the-vacuum-algorithm-v3.diff
Description: Binary data
