From: Laurenz Albe <laurenz.albe@cybertec.at>
To: jian xu <jamesxu@outlook.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: how to calibrate the cost model parameters
Date: Mon, 30 May 2022 13:23:51 +0200
Message-ID: <68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at> (raw)
In-Reply-To: <BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
References: <BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com>
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
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: laurenz.albe@cybertec.at, jamesxu@outlook.com
Subject: Re: how to calibrate the cost model parameters
In-Reply-To: <68bd3f240bc5792eb098418656445c3ebd0488c5.camel@cybertec.at>
* 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