Message-ID: <68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at>
Subject: Re: how to calibrate the cost model parameters
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: jian xu <jamesxu@outlook.com>, "pgsql-admin@postgresql.org"
	 <pgsql-admin@postgresql.org>
Date: Mon, 30 May 2022 13:23:51 +0200
In-Reply-To: 
 <BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
References: 
	<BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/68bd3f240bc5792eb098418656445c3ebd0488c5.camel%40cybertec.at>
Precedence: bulk

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