Hi Amit, Kirk >One idea could be to remove "nBlocksToInvalidate < >BUF_DROP_FULL_SCAN_THRESHOLD" part of check "if (cached && >nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)" so that it always >use optimized path for the tests. Then use the relation size as >NBuffers/128, NBuffers/256, NBuffers/512 for different values of >shared buffers as 128MB, 1GB, 20GB, 100GB.
I followed your idea to remove check and use different relation size for different shared buffers as 128M,1G,20G,50G(my environment can't support 100G, so I choose 50G). According to results, all three thresholds can get optimized, even NBuffers/128 when shared_buffers > 128M. IMHO, I think NBuffers/128 is the maximum relation size we can get optimization in the three thresholds, Please let me know if I made something wrong. Recovery after vacuum test results as below ' Optimized percentage' and ' Optimization details(unit: second)' shows: (512),(256),(128): means relation size is NBuffers/512, NBuffers/256, NBuffers/128 %reg: means (patched(512)- master(512))/ master(512) Optimized percentage: shared_buffers %reg(512) %reg(256) %reg(128) ----------------------------------------------------------------- 128M 0% -1% -1% 1G -65% -49% -62% 20G -98% -98% -98% 50G -99% -99% -99% Optimization details(unit: second): shared_buffers master(512) patched(512) master(256) patched(256) master(128) patched(128) ----------------------------------------------------------------------------------------------------------------------------- 128M 0.108 0.108 0.109 0.108 0.109 0.108 1G 0.310 0.107 0.410 0.208 0.811 0.309 20G 94.493 1.511 188.777 3.014 380.633 6.020 50G 537.978 3.815 867.453 7.524 1559.076 15.541 Test prepare: Below is test table amount for different shared buffers. Each table size is 8k, so I use table amount = NBuffers/(512 or 256 or 128): shared_buffers NBuffers NBuffers/512 NBuffers/256 NBuffers/128 ------------------------------------------------------------------------------------------- 128M 16384 32 64 128 1G 131072 256 512 1024 20G 2621440 5120 10240 20480 50G 6553600 12800 25600 51200 Besides, I also did single table performance test. Still, NBuffers/128 is the max relation size which we can get optimization. Optimized percentage: shared_buffers %reg(512) %reg(256) %reg(128) ----------------------------------------------------------------- 128M 0% 0% -1% 1G 0% 1% 0% 20G 0% -24% -25% 50G 0% -24% -20% Optimization details(unit: second): shared_buffers master(512) patched(512) master(256) patched(256) master(128) patched(128) ----------------------------------------------------------------------------------------------------------------------------- 128M 0.107 0.107 0.108 0.108 0.108 0.107 1G 0.108 0.108 0.107 0.108 0.108 0.108 20G 0.208 0.208 0.409 0.309 0.409 0.308 50G 0.309 0.308 0.408 0.309 0.509 0.408 Any question on my test results is welcome. Regards, Tang
