From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Charles Nadeau Sent: Monday, July 10, 2017 11:48 AM To: Andreas Kretschmer <andreas@a-kretschmer.de> Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Very poor read performance, query independent
Andreas,
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.
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 teffective_cache_size should be set around 64GB (again considering that Postgres is the only app running on the server).
