Ask for disk type and set page_cost about pgtune HOT 5 CLOSED

Hi @le0pard,
a safe change for everyone would be this:

if SSD:
  random_page_cost = 1.1
else
  random_page_cost = 4

Technically the only thing it changes is Postgres using indexes more eagerly, affecting only planner, so nothing to benchmark - some plans query plans switch to using indexes. random_page_cost basically says "you'd better read 4x more data sequentially than read it by random index scan".

Setting random_page_cost = 1.1 on SSD is pretty modest change. Further tweaking for system may get it lower, down to seq_page_cost.

Changing seq_page_cost is not normally necessary, what matters is ratio between random and seq scan - you need to get data from disk, and you'll get it either randomly reading or sequentially reading :)

This change is critical for anyone who uses PostGIS as their index operations are much faster than on-disk operations, and there is currently no way in Postgres to inform planner about it.

It's currently number one reason for "why are my indexes not being used?!" when debugging someone's query in https://t.me/pgsql. Setting 1.1 usually solves issues.

Postgres developers recommend going as low as 1.0: http://www.sai.msu.su/~megera/postgres/talks/Everything-Indexes-v4.pdf

from pgtune.

@dewey so still not clear what the math model?

if SSD
 random_page_cost = ??? # does memory, cpu influence?
 seq_page_cost = ??? # does memory, cpu influence?
else
  random_page_cost = ??? # does memory, cpu influence?
 seq_page_cost = ??? # does memory, cpu influence?

from pgtune.

I don't know enough about performance tuning to suggest values. I just came across your tool and the article at the same time and figured it would be a good match to be included.

If there's nobody who can give a bit more background information you could also just add a selector for the disk and if SSD is suggested link to the blog post or Postgres documentation about random_page_cost and seq_page_cost. Judging by the comments on HN (https://news.ycombinator.com/item?id=15761026) a lot of people didn't know about these values in the first place.

One data point from there is:

Setting random_page_cost = 1 is pretty common advice for SSD and works well in my experience. Typical advice for HDD RAID or SCSI is random_page_cost = 2 and SSDs are faster than them.

from pgtune.

@dewey so this mean, we do not have benchmark for random_page_cost and seq_page_cost (because by this numbers we can create math model and add it to pgtune). By "feeling" can give wrong values and create problem for using pgtune tool, because "do no harm" main point for it.

from pgtune.

@Komzpa Thanks, added in pgtune. You can check with @dewey :)

from pgtune.