0001-Lower-random_page_cost-default-to-1.2-and-update-docs-about-it.patch

From 50f4800e3501a84c0d41a5e29cf384bf465f230d Mon Sep 17 00:00:00 2001
From: Greg Sabino Mullane <greg@turnstep.com>
Date: Mon, 30 Sep 2024 09:52:28 -0400
Subject: [PATCH] Lower random_page_cost default to 1.2 and update docs about
 it.

---
 doc/src/sgml/config.sgml                      | 35 ++++++-------------
 src/backend/utils/misc/postgresql.conf.sample |  2 +-
 2 files changed, 12 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 0aec11f443..e901a71613 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5728,14 +5728,15 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       <listitem>
        <para>
         Sets the planner's estimate of the cost of a
-        non-sequentially-fetched disk page.  The default is 4.0.
-        This value can be overridden for tables and indexes in a particular
-        tablespace by setting the tablespace parameter of the same name
-        (see <xref linkend="sql-altertablespace"/>).
-       </para>
+        non-sequentially-fetched disk page.  The default is <literal>1.2</literal>.
+        Note that this value assumes use of a solid-state drive, which
+        has a low random read cost relative to sequential scans. If you
+        are a using a hard disk drive, raising the value to <literal>3</literal>
+        or <literal>4</literal> may give you better performance.
+        </para>
 
        <para>
-        Reducing this value relative to <varname>seq_page_cost</varname>
+        Reducing <varname>random_page_cost</varname> relative to <varname>seq_page_cost</varname>
         will cause the system to prefer index scans; raising it will
         make index scans look relatively more expensive.  You can raise
         or lower both values together to change the importance of disk I/O
@@ -5744,24 +5745,10 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
        </para>
 
        <para>
-        Random access to mechanical disk storage is normally much more expensive
-        than four times sequential access.  However, a lower default is used
-        (4.0) because the majority of random accesses to disk, such as indexed
-        reads, are assumed to be in cache.  The default value can be thought of
-        as modeling random access as 40 times slower than sequential, while
-        expecting 90% of random reads to be cached.
-       </para>
-
-       <para>
-        If you believe a 90% cache rate is an incorrect assumption
-        for your workload, you can increase random_page_cost to better
-        reflect the true cost of random storage reads. Correspondingly,
-        if your data is likely to be completely in cache, such as when
-        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>.
+        This value can be overridden for tables and indexes in a particular
+        tablespace by setting the tablespace parameter of the same name
+        (see <xref linkend="sql-altertablespace"/>). It can also be set
+        per-database and per-role, as needed.
        </para>
 
        <tip>
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 667e0dc40a..424df4a868 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -417,7 +417,7 @@
 # - Planner Cost Constants -
 
 #seq_page_cost = 1.0			# measured on an arbitrary scale
-#random_page_cost = 4.0			# same scale as above
+#random_page_cost = 1.2			# 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
-- 
2.30.2