From: Tom Lane <tgl@sss.pgh.pa.us>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: jian xu <jamesxu@outlook.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: how to calibrate the cost model parameters
Date: Tue, 31 May 2022 10:44:14 -0400
Message-ID: <175811.1654008254@sss.pgh.pa.us> (raw)
In-Reply-To: <1ec517a25fd893f783e3bd4606eccf036b0c6696.camel@cybertec.at>
References: <BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
<68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at>
<BL0PR02MB4561DCC0C83D9EF094313AA0A1DD9@BL0PR02MB4561.namprd02.prod.outlook.com>
<CAMkU=1zoO1HhZL=DakUcnpiHDOWWBA-HHBsWB7irtNnYb207uQ@mail.gmail.com>
<1ec517a25fd893f783e3bd4606eccf036b0c6696.camel@cybertec.at>
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> 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.
The 4.0 value *is* based on some real evidence --- many years ago, I put
in a lot of machine time to get an estimate of that for non-cached fetches
on the hardware I was using at the time. If somebody did that over again
today, they'd likely get a somewhat different value, even assuming they
were still using spinning-rust storage. But it doesn't matter too much.
A value around that is probably fine for rotating storage, while if you
are using SSD it's likely better to use something around 1.0, and beyond
that any inaccuracies in the cost constants are usually swamped by
imperfections of the cost models and other data. For instance, it's not
that useful to sweat about what the right value is for cpu_operator_cost
when we've never spent any serious effort on assigning plausible procost
factors to different functions. (Worse, the whole notion that a given
function has a fixed cost independent of its input data is sadly
inadequate.)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, laurenz.albe@cybertec.at, jeff.janes@gmail.com, jamesxu@outlook.com
Subject: Re: how to calibrate the cost model parameters
In-Reply-To: <175811.1654008254@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox