On Tue, 15 Oct 2024 at 10:15, Bruce Momjian <br...@momjian.us> wrote: > I am not a fan of this patch. I don't see why _removing_ the magnetic > part helps because you then have no logic for any 1.2 was chosen. I > would put the magnetic in a separate paragraph perhaps, and recommend > 4.0 for it. Also, per-tablespace makes sense because of physical media > differences, but what purpose would per-database and per-role serve? > Also, per-tablespace is not a connection-activated item like the other > two.
Yeah, I think any effort to change the default value for this setting would require some analysis to prove that the newly proposed default is a more suitable setting than the current default. I mean, why 1.2 and not 1.1 or 1.3? Where's the evidence that 1.2 is the best value for this? I don't think just providing evidence that random read times are closer to sequential read times on SSDs are closer than they are with HDDs is going to be enough. What we want to know is if the planner costs become more closely related to the execution time or not. From my experience, random_page_cost really only has a loose grasp on reality, so you might find that it's hard to prove this with any degree of confidence (just have a look at how inconsiderate index_pages_fetched() is to other queries running on the database, for example). I suggest first identifying all the locations that use random_page_cost then coming up with some test cases that run queries that exercise those locations in a way that does things like vary the actual selectivity of some value to have the planner switch plans then try varying the random_page_cost to show that the switchover point is more correct with the new value than it is with the old value. It would be nice to have this as a script so that other people could easily run it on their hardware to ensure that random_page_cost we choose as the new default is representative of the average hardware. You'll likely need to do this with varying index sizes. I imagine to properly test this so that we'd have any useful degree of confidence that the new value is better than the old one would likely require a benchmark that runs for several hours. At the upper end, you'd likely want the data sizes to exceed the size of RAM. Another dimension that the tests should likely explore is varying data locality. David
