After reducing random_page_cost to 4 and testing more, I can report that the aggregate read throughput for parallel sequential scan is about 90MB/s. However the throughput for sequential scan is still around 4MB/s.
One more question: if a query uses more than one table, can more than one table be read through a parallel sequential scan? I have many queries joining tables and I noticed that there was never more than one table read in parallel. Thanks! Charles On Mon, Jul 10, 2017 at 8:35 PM, Igor Neyman <iney...@perceptron.com> wrote: > > > *From:* pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] *On Behalf Of *Charles Nadeau > *Sent:* Monday, July 10, 2017 11:48 AM > *To:* Andreas Kretschmer <andr...@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. > > Thanks! > > > > Charles > > > > On Mon, Jul 10, 2017 at 5:35 PM, Andreas Kretschmer < > andr...@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 > > > > > > > > > -- Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/
