Some more thoughts on random_page_cost

A mates months backmost I posted about possibly adjusting random_page_cost to amended bespeak really current storage handles random and sequential access. I had a bunch of great discussions astir the taxable since then, but yet I got distracted by different stuff.

POSETTE happened past week, pinch my pre-recorded talk about this very taxable (and galore different awesome talks, BTW). Which reminded me that I started reasoning astir random_page_cost a spot differently. So here’s an update pinch immoderate much thoughts.

I did already touch connected immoderate of these things successful the aged post, and then also successful the POSETTE talk successful much detail.

Before I get to that, fto maine stock a floor plan pinch random_page_cost results from rotational SATA drives. I wholly forgot I person these disks successful the instrumentality until I opened the lawsuit to do immoderate maintenance. Those are apt overmuch person to the retention utilized for the original experiments successful ~2000. Those would apt beryllium PATA aliases SCSI drives, but still rotational. Maybe that will springiness america values person to the 4.0 default?

Clearly not. In fact, the estimated random_page_cost is ~125, about 2-4x the estimate for SSD storage. So pinch SSDs it’s getting person to the default, but that’s conscionable a coincidence.

Perhaps there’s immoderate basal portion of the aged research that we failed to recall? Or possibly the “raw” results were adjusted successful immoderate way. But it seems the 4.0 default ne'er was the “raw” costs of random I/O.

I sewage a batch of feedback from group who tried expanding random_page_cost in the past. In their acquisition it decidedly did not amended the performance, it hurt it. How is that possible, if it makes the costing little accurate?

I judge it comes to random_page_cost “compensating” for the costs exemplary being incomplete. It’s not accounting for various caching effects and resources related to plans performing a batch of random I/O.

Every costs exemplary is an approximation, and a comparatively crude one. It’s not possible to person a fast/cheap costs exemplary that accurately mimics each tiny detail. You whitethorn make it much and much detailed, but astatine immoderate constituent it’d become as ample arsenic the original system. And past why person a model? It’d beryllium pretty useless.

Our costs exemplary has a mates gaps that I deliberation matter here.

Cost exemplary (mostly) ignores memory

The costs of an cognition is calculated from the magnitude of CPU and I/O utilized to execute the operation. Those are 2 cardinal resources, but it ignores memory

• another important resource.

We person work_mem, but that’s much of a information limit, arsenic it limits the size of work buffers (e.g. for sorting aliases hashing). And it whitethorn impact the magnitude of I/O an cognition needs to do (e.g. smaller buffers successful hash subordinate intends more spilling to disk).

But it does not way different representation “used” by a plan, it ignores different uses of memory.

Consider a 100GB table, containing 1GB of “interesting” information (matching our query predicate). We tin scan the array sequentially aliases done an index. If the array is “cold”, a sequential scan whitethorn push ~100GB of different information from memory (shared buffers aliases page cache). The query whitethorn tally conscionable good with work_mem=4MB, but it whitethorn efficaciously “use” 100GB of memory.

On the different hand, an scale whitethorn only request to entree ~1% of the array (the 1GB of absorbing data). The random I/O whitethorn easy return much clip than the sequential scan, but connected the different manus it’ll only “use” 1GB of memory. It may evict overmuch little different information from cache.

Yet the costs exemplary is wholly oblivious to this.

Locality of access

Another logic is the conception of an progressive set. You whitethorn person a 1TB database, but successful astir applicable systems only a mini subset of the information is accessed. Users only look astatine caller orders, etc. This is what we telephone the “active set” of the database.

The cardinal is to support the progressive group successful memory. And plans doing a batch of random I/O thin to beryllium much localized, i.e. accessing only the “interesting” data. An index scan will typically entree a overmuch smaller fraction of information than a sequential scan. The I/O whitethorn beryllium much costly (random, visiting pages multiple times), but it’s conscionable the “interesting” data.

Sequential scans whitethorn massively grow the progressive group - perchance to the whole database. Which is not great, unless you really person capable RAM to fresh the whole database. Index scans let for a overmuch smaller progressive set.

But the planner is wholly unaware of the progressive set. It plans arsenic if all queries commencement pinch acold data. It considers caching effects only successful very limited cases - e.g. caching wrong the aforesaid query.

The planner is oblivious to various different things. It plans queries successful isolation, as if location were nary different queries competing for the aforesaid resources and the bandwidth was infinite. A sequential scan whitethorn beryllium awesome for a azygous backend, but when location are 100 backends each doing seqscans, you’ll deed retention bandwidth.

I now spot random_page_cost arsenic a proxy for each these effects.

The planner is not costing representation the aforesaid measurement arsenic CPU/disk, nor does it understand caching effects. Lower random_page_cost values push it toward plans that are much localized and support the progressive group nether control. Which in the agelong tally seems for illustration the winning strategy.

This intends location can’t beryllium a instrumentality that’d show you the “right” worth for random_page_cost aft profiling your retention and measuring really it handles sequential and random I/O. I’d emotion to person that, but if random_page_cost is not a earthy I/O cost, that would not work.

Tuning random_page_cost has to beryllium system-specific, driven by feedback from monitoring. For example, you whitethorn look astatine the apical queries successful pg_stat_statements, and spot if those would use from a little random_page_cost value. Then effort to adjust the value, and show if it has the desired effect (or if it hurts other queries).