Re: Dynamic gathering the values for seq_page_cost/xxx_cost

>
>
> It's probably worth testing on various other storage systems to see
>> how that applies to those.
>>
>> Yes, I can test more on new hardware once I get it. Now it is still in
> progress.
> However I can only get a physical machine with SSD or Virtual machine with
> SSD, other types are hard for me right now.
>
>
Here is a result on a different hardware. The test method is still not
changed.[1]

Hardware Info:

Virtual Machine with 61GB memory.
Linux Kernel: 5.4.0-31-generic Ubuntu

# lshw -short -C disk
H/W path Device Class Description
=====================================================
/0/100/4/0 /dev/vda disk 42GB Virtual I/O device
/0/100/5/0 /dev/vdb disk 42GB Virtual I/O device

The disk on the physical machine is claimed as SSD.

This time the FIO and my tools can generate the exact same result.

fs_cache_lat = 0.957756us, seq_read_lat = 70.780327us, random_page_lat =
438.837257us

cache hit ratio: 1.000000 random_page_cost 1.000000
cache hit ratio: 0.900000 random_page_cost 5.635470
cache hit ratio: 0.500000 random_page_cost 6.130565
cache hit ratio: 0.100000 random_page_cost 6.192183
cache hit ratio: 0.000000 random_page_cost 6.199989

| | seq_read_lat(us) | random_read_lat(us) |
| FIO | 70 | 437 |
| MY Tool | 70 | 438 |

The following query plans have changed because we change random_page_cost
to 4
to 6.2, the Execution time also changed.

| | random_page_cost=4 | random_page_cost=6.2 |
|-----+--------------------+----------------------|
| Q1 | 2561 | 2528.272 |
| Q10 | 4675.749 | 4684.225 |
| Q13 | 18858.048 | 18565.929 |
| Q2 | 329.279 | 308.723 |
| Q5 | 46248.132 | 7900.173 |
| Q6 | 52526.462 | 47639.503 |
| Q7 | 27348.900 | 25829.221 |

Q5 improved by 5.8 times and Q6 & Q7 improved by ~10%.

[1]
https://www.postgresql.org/message-id/CAKU4AWpRv50k8E3tC3tiLWGe2DbKaoZricRh_YJ8y_zK%2BHdSjQ%40mail.gmail.com

--
Best Regards
Andy Fan