Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t4BRw-00Gq6w-E3 for pgsql-hackers@arkaria.postgresql.org; Fri, 25 Oct 2024 03:57:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t4BRt-00BYf7-5R for pgsql-hackers@arkaria.postgresql.org; Fri, 25 Oct 2024 03:57:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t4BRs-00BYez-QL for pgsql-hackers@lists.postgresql.org; Fri, 25 Oct 2024 03:57:17 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t4BRm-002qY2-CW for pgsql-hackers@postgresql.org; Fri, 25 Oct 2024 03:57:16 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-6e5cec98cceso14043067b3.2 for ; Thu, 24 Oct 2024 20:57:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729828628; x=1730433428; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=eb5u/ArRIZFvPyP2s48geRny7TlR6sKjMj2dBLcQ3Rw=; b=QTAucJpH/vzhDarWNoOo/b9NNqatqTSCMZ3AglXh1JTQbB5B43Lw4UTGF8VYmbcprr Vw2gH5lv6P65RVvbETHIUNUoNuH7FUautJ7/fAoHoDVqJ61G74l498xdvthbyUQazGsX 9dWagWpoI1paZfjkDnujw1byoynBRS3Nq5YOb05E5gJBpUkRRC+NwdfwTFD14MSarRkF Du2DTrDY0ljh4yANPcq9P9M7cAE1TTIBpeMNd8p4uTr3J+F1DWp+ZKdLdVGTJYWJoupu uqvq3NvW2uDzFC3ay8tjcFqzVa/CnU+icqIaiJAZbBBs6Mp1PI1jEqGTjA5xY+WPBpto 6i5A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729828628; x=1730433428; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=eb5u/ArRIZFvPyP2s48geRny7TlR6sKjMj2dBLcQ3Rw=; b=IHxHGKdRV9UxFpB+Exq0MMWEsiS5Vur160kbZIUMkVZCtooh1E1DcdU7Nt0hVEoy7p qsHY0d7UPDGUnI67oB82pYZD64NrWvwBfruPvEqAqk9RmcPdzCT2kjBmh/PF3pgPtrv2 26B9q+hJzR+Ggur+NiVXIfve0IR+k4g1irK0+Gj4xUJedGIKOSfsE2KnCtTVMSOw1Dzs iA41fOHRb9T5Zy6kyyMpHeJ8JdkLe7Kd62ShyH43G1mCL+jahaGetrRpIFzPkR9zrzYi 7etwIY9cJfCd4RS/Wu8fA3Rp5LcXAOrk2uF7qc9WFmzAqSfwA6YsfjF0pPl/kdB/v+KQ 5tqQ== X-Gm-Message-State: AOJu0YwaN6MBFlW0yRwbWCMRRQyA4htaFd1XePFmtNOOZRaRNamzEi3S MU0L+q4SvYAydoCPhK7WGBUYSwA/n+V2cIvk7/zTNdT3/bd27KB7ZIC1Ohd2fVcVIZLeUTtqMub ZTaPXgGExfNmMcEazEEuyptOhmE3v0Dgt X-Google-Smtp-Source: AGHT+IFp3BxB3Ey7LcUTr6n32FY43M7bfk7vOOiuPJb8IQu+sYS1aS9tffm6C9fA1FCwSp57oBgTS5hKIRNDvuA4Y64= X-Received: by 2002:a05:690c:6f12:b0:6e3:1050:5a3b with SMTP id 00721157ae682-6e7f0e53fc6mr99173827b3.25.1729828628588; Thu, 24 Oct 2024 20:57:08 -0700 (PDT) MIME-Version: 1.0 References: <877caxaxt6.fsf@wibble.ilmari.org> In-Reply-To: From: wenhui qiu Date: Fri, 25 Oct 2024 11:56:57 +0800 Message-ID: Subject: Re: Changing the default random_page_cost value To: Greg Sabino Mullane Cc: pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000edf1e10625451c4c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000edf1e10625451c4c Content-Type: text/plain; charset="UTF-8" HI Greg Sabino Mullane Another thing is that you simply change the configuration template is not effective, need to modify the DEFAULT_RANDOM_PAGE_COST values { {"random_page_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of a " "nonsequentially fetched disk page."), NULL, GUC_EXPLAIN }, &random_page_cost, DEFAULT_RANDOM_PAGE_COST, 0, DBL_MAX, NULL, NULL, NULL }, src/include/optimizer/cost.h /* defaults for costsize.c's Cost parameters */ /* NB: cost-estimation code should use the variables, not these constants! */ /* If you change these, update backend/utils/misc/postgresql.conf.sample */ #define DEFAULT_SEQ_PAGE_COST 1.0 #define DEFAULT_RANDOM_PAGE_COST 4.0 #define DEFAULT_CPU_TUPLE_COST 0.01 #define DEFAULT_CPU_INDEX_TUPLE_COST 0.005 #define DEFAULT_CPU_OPERATOR_COST 0.0025 #define DEFAULT_PARALLEL_TUPLE_COST 0.1 #define DEFAULT_PARALLEL_SETUP_COST 1000.0 Thanks > > > > --000000000000edf1e10625451c4c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

HI Greg Sabino= Mullane
=C2=A0 =C2=A0=C2=A0Another thing is that you simply change= the configuration template is not effective,

need= to modify the=C2=A0DEFAULT_RANDOM_PAGE_COST values
<= div>
{
{"random_page_cost", PGC_USERSET, Q= UERY_TUNING_COST,
gettext_noop("Sets t= he planner's estimate of the cost of a "
= "nonsequential= ly fetched disk page."),
NULL,
GUC_EXPLAIN
},
= &random_page_cost,
DEFAULT_RANDOM_PAGE_COST, 0, D= BL_MAX,
NUL= L, NULL, NULL
},

src/include/optimizer/cost.h
/* default= s for costsize.c's Cost parameters */
/* NB: cost-estimation code should= use the variables, not these constants! */
/* If you change these, update b= ackend/utils/misc/postgresql.conf.sample */
#define DEFAULT_SEQ_PAGE_COST 1.= 0
#define DEFAULT_RANDOM_PAGE_COST 4.0
#define DE= FAULT_CPU_TUPLE_COST 0.01
#define DEFAULT_CPU_INDEX_TUPLE_COST 0.005
#define D= EFAULT_CPU_OPERATOR_COST 0.0= 025
#define = DEFAULT_PARALLEL_TUPLE_COST <= span style=3D"color:rgb(235,200,141)">0.1
#define DEFAULT_PARALLEL_SETUP_COST 1000.0

Thanks=C2= =A0



--000000000000edf1e10625451c4c--