Re: [PERFORM] Very poor read performance, query independent

Because the ratio between the Sequential IOPS and Random IOPS is about 29. Taking into account that part of the data is in RAM, I obtained an "effective" ratio of about 22.

Thanks!

Charles

On Mon, Jul 10, 2017 at 5:35 PM, Andreas Kretschmer <andreas@a-kretschmer.de> wrote:

Am 10.07.2017 um 16:03 schrieb Charles Nadeau:

random_page_cost | 22

why such a high value for random_page_cost?

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Charles Nadeau Ph.D.
http://charlesnadeau.blogspot.com/

Considering RAM size of 72 GB and your database size of ~225GB, and also the fact that Postgres is the only app running on the server, probably 1/3 of your database resides in memory, so random_page_cost = 22 looks extremely high, probably it completely precludes index usage in your queries.

You should try this setting at least at its default value: random_page_cost =4, and probably go even lower.

Also, effective_cache_size is at least as big as your shared_buffers. Having 72GB RAM t effective_cache_size should be set around 64GB (again considering that Postgres is the only app running on the server).

Regards,

Igor Neyman