We have a table with more than 300 million rows and two indexes with one column. From time to time, the application takes to flight. At the same time, there is a high index for the insert statement for this table. I also noticed a large amount of buffer. Can someone help me fix this problem?
The following are statistics to confirm when the index is high and performance problems.
Total Per Execution Per Row
Executions 51,857 1 1.00
Elapsed Time (sec) 3,270.67 0.06 0.06
CPU Time (sec) 1,554.41 0.03 0.03
Buffer Gets 140,844,228 2,716.01 2,716.01
Disk Reads 1,160 0.02 0.02
Direct Writes 0 0.00 0.00
Rows 51,857 1.00 1
Fetches 0 0.00 0.00
Same statement, same time range, same workload.
Total Per Execution Per Row
Executions 94,424 1 1.00
Elapsed Time (sec) 30.41 <0.01 <0.01
CPU Time (sec) 12.90 <0.01 <0.01
Buffer Gets 1,130,297 11.97 11.97
Disk Reads 469 <0.01 <0.01
Direct Writes 0 0.00 0.00
Rows 94,424 1.00 1
Fetches 0 0.00 0.00
source
share