Index bloat reduced in PostgreSQL v14

Re:Index bloat reduced in PostgreSQL v14 by arp242 on Saturday September 11, 2021 @05:37AM (#28490630)

Try lowering the random_page_cost value; this is the performance cost query planner uses for random reads, which is usually too high if you're using an SSD where random reads are cheap (on disks it's expensive). Just setting it to 1 works well in my case.

This solves many "it does a slow seq scan even though there's an index"-cases.

https://postgresqlco.nf/doc/en/param/random_page_cost/

There are some other query planner knobs you can tune as well; the https://postgresqlco.nf site is pretty good.

[ Parent | on HN ]

Re:Index bloat reduced in PostgreSQL v14 by ahachete on Saturday September 11, 2021 @07:36AM (#28491094)

If using SSD or similar fast storage subsystem, or those that hide a higher random access time vs sequential, you may indeed want to reduce random_page_cost to make random_page_cost / seq_page_cost in the 1.2-1.5 range.

But it's also wise to review the default_statistics_target being used, that autovacuum is running frequently enough (which does autoanalyze), that the analyze thresholds are also properly tuned...

Thank you for mentioning https://postgresqlco.nf Team member here :) All these parameters mentioned here are well documented there, with recommendations.

Also, have you tried the Tuning Guide? (https://postgresqlco.nf/tuning-guide)

[ Parent | on HN ]