how to calibrate the cost model parameters

From: jian xu <jamesxu(at)outlook(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: how to calibrate the cost model parameters
Date: 2022-05-29 15:29:55
Message-ID: BL0PR02MB45619FD7E496D38966D33E5CA1DA9@BL0PR02MB4561.namprd02.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,
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.

Thanks,

James

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Raul Kaubi 2022-05-30 08:00:02 Amcheck extension query for tables (verify_heapam)
Previous Message hubert depesz lubaczewski 2022-05-28 14:05:05 Re: ERROR: found xmin 2706850882 from before relfrozenxid 126900049