Date: Mon, 27 Apr 2020 12:16:30 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: yigong hu <yigongh@gmail.com>, splarv@ya.ru,
	pgsql-docs@lists.postgresql.org
Subject: Re: explanation for random_page_cost is outdated
Message-ID: <20200427161630.GB21731@momjian.us>
References: <20200319205646.GB28431@momjian.us>
 <72492300-BFD4-4CCF-9136-20CC41A18A10@ya.ru>
 <CAOxFffcourucFqSk+tZA13ErS3XRYkDy6EeaPff4AvHGiEEuug@mail.gmail.com>
 <CAFj8pRA35t++NeQ2mbT=3O9=o7M0hrsRZUmPOCRErZR7Am5A_A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="mP3DRpeJDSE+ciuQ"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAFj8pRA35t++NeQ2mbT=3O9=o7M0hrsRZUmPOCRErZR7Am5A_A@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--mP3DRpeJDSE+ciuQ
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Mon, Apr 27, 2020 at 06:02:41AM +0200, Pavel Stehule wrote:
> 
> 
> ne 26. 4. 2020 v 21:25 odesílatel yigong hu <yigongh@gmail.com> napsal:
> 
>     Sorry to hijack the thread, I also recently have similar observation that
>     the statement about random_page_cost on SSD is ambiguous. The current
>     document says that
> 
>     > Storage that has a low random read cost relative to sequential, e.g.
>     solid-state drives, might also be better modeled with a lower value for
>     random_page_cost.
> 
>     However, this statement does not clarify what values might be good. For
>     some workload, the default value 4.0 would cause bad performance and
>     lowering random_page_cost to a value 3.0 or 2.0 does not solve the
>     performance problem. Only when the random_page_cost is lowered to below 1.2
>     will the bad performance be mitigated. Thus, I would suggest elaborating on
>     this description further as:
> 
>      >  Storage that has a low random read cost relative to sequential, e.g.
>     solid-state drives, might also be better modeled with a value that is close
>     to 1 for random_page_cost.
> 
> 
> I depends on estimation. Lot of people use random_page_cost as fix of broken
> estimation. Then configures this value to some strange values. Lot of other
> queries with good estimation can be worse then.

I have been recommending 1.1 as a value for random_page_cost for SSDs
for years, and I think it would be helpful to suggest that value, so doc
patch attached.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--mP3DRpeJDSE+ciuQ
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="random.diff"

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index a14df06..38ae273
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*************** ANY <replaceable class="parameter">num_s
*** 4915,4921 ****
          the database is smaller than the total server memory, decreasing
          random_page_cost can be appropriate.  Storage that has a low random
          read cost relative to sequential, e.g. solid-state drives, might
!         also be better modeled with a lower value for random_page_cost.
         </para>
  
         <tip>
--- 4915,4922 ----
          the database is smaller than the total server memory, decreasing
          random_page_cost can be appropriate.  Storage that has a low random
          read cost relative to sequential, e.g. solid-state drives, might
!         also be better modeled with a lower value for random_page_cost,
!         e.g., <literal>1.1</literal>.
         </para>
  
         <tip>

--mP3DRpeJDSE+ciuQ--