On Tue, Sep 22, 2020 at 10:57 AM Andy Fan <zhihui.fan1...@gmail.com> wrote: > > > My tools set the random_page_cost to 8.6, but based on the fio data, it > should be > set to 12.3 on the same hardware. and I do see the better plan as well with > 12.3. > Looks too smooth to believe it is true.. > > The attached result_fio_mytool.tar.gz is my test result. You can use git > show HEAD^^ > is the original plan with 8.6. git show HEAD^ show the plan changes after > we changed > the random_page_cost. git show HEAD shows the run time statistics changes for > these queries. > I also uploaded the test tool [1] for this for your double check.
The scripts seem to start and stop the server, drop caches for every query. That's where you are seeing that setting random_page_cost to fio based ratio provides better plans. But in practice, these costs need to be set on a server where the queries are run concurrently and repeatedly. That's where the caching behaviour plays an important role. Can we write a tool which can recommend costs for that scenario? How do the fio based cost perform when the queries are run repeatedly? -- Best Wishes, Ashutosh Bapat
