Thanks Laurenz. It is ok that these values are "arbitrary scale", but we still need to calibrate them to get the correct
"arbitrary scale". For example if
seq_page_cost is 1 and cpu_tuple_cost is 0.01, how to verify the io seq cost is 100 times slower than cpu access cost？
"enable_seqscan" and "enable_nestloop" can work in some cases, but it doesn’t work with any cases.
Does anyone have experience to calibrate the cost model parameters to get the correct value? Thanks
James
On Sun, 2022-05-29 at 15:29 +0000, jian xu wrote:
> On my system, the estimated query cost has huge difference with the real query cost.
> For example, a simple select query, the estimated query cost is 100X bigger than real cost,
> I think I need to calibrate the cost model parameters below(I still use default value)
>
> #seq_page_cost = 1.0 # measured on an arbitrary scale
> #random_page_cost = 4.0 # same scale as above
> #cpu_tuple_cost = 0.01 # same scale as above
> #cpu_index_tuple_cost = 0.005 # same scale as above
> #cpu_operator_cost = 0.0025 # same scale as above
>
> I googled it, and only find some suggestions, for example set random_page_cost to 1 or 2
> for SSD, however, there is no doc about how to calibrate the cost model parameters to make
> the estimated query cost close to the real query cost.
That is as designed, see that "arbitrary scale" comment on "seq_page_cost".
Sure, you could adjust all cost parameters so that 1 roughly corresponds to
the number of milliseconds for the actual query, but there is little benefit
in that.
The numbers are only used to compare plans with each other. Moreover, settings
like "enable_seqscan" and "enable_nestloop" can artificially distort the numbers.
Yours,
Laurenz Albe
