Message-ID: <1ec517a25fd893f783e3bd4606eccf036b0c6696.camel@cybertec.at>
Subject: Re: how to calibrate the cost model parameters
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Jeff Janes <jeff.janes@gmail.com>, jian xu <jamesxu@outlook.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Date: Tue, 31 May 2022 08:46:29 +0200
In-Reply-To: 
 <CAMkU=1zoO1HhZL=DakUcnpiHDOWWBA-HHBsWB7irtNnYb207uQ@mail.gmail.com>
References: 
	<BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
	 <68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at>
	 <BL0PR02MB4561DCC0C83D9EF094313AA0A1DD9@BL0PR02MB4561.namprd02.prod.outlook.com>
	 <CAMkU=1zoO1HhZL=DakUcnpiHDOWWBA-HHBsWB7irtNnYb207uQ@mail.gmail.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/1ec517a25fd893f783e3bd4606eccf036b0c6696.camel%40cybertec.at>
Precedence: bulk

On Mon, 2022-05-30 at 17:12 -0400, Jeff Janes wrote:
> On Mon, May 30, 2022 at 3:40 PM jian xu <jamesxu@outlook.com> wrote:
> > 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
> 
> I put a lot of time into it, and I think you will find that there is no consistent way to do so.
> For cpu_tuple_cost, for example, are the hint bits already set?  Do they need to get set?
> Are the clog pages still in memory?  Is there contention on the lock used to determine if a given
> transaction is still running?  How many columns does the table have? How many of them need to be
> accessed for the current case, and how far left and right are they in the table and are they toasted?
> 
> For seq reads, how much contention is there for the buffer mapping partition lock?  What is the
> throughput of your IO system?  How many other seq reads will be occuring at the same time? Etc.
> 
> For any change you intend to make, do you have a good enough load generator and test system set
> up so you can test that it doesn't make something else worse?

+1

I think that the values of those parameters are more set by experience and tradition than
by measurable physical evidence.  In a way, it just happens to work (mostly).
For example, who says that on a spinning disk, random I/O is four times as slow as
sequential I/O?  Very likely, you have to factor in that part of the I/O requests are
satisfied from the kernel page cache.

Yours,
Laurenz Albe