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

Hmm - how are you measuring that sequential scan speed of 4MB/s? I'd
recommend doing a very simple test e.g, here's one on my workstation -
13 GB single table on 1 SATA drive - cold cache after reboot, sequential
scan using Postgres 9.6.2:

bench=#  EXPLAIN SELECT count(*) FROM pgbench_accounts;
                                      QUERY PLAN
------------------------------------------------------------------------------------
  Aggregate  (cost=2889345.00..2889345.01 rows=1 width=8)
    ->  Seq Scan on pgbench_accounts (cost=0.00..2639345.00
rows=100000000 width=0)
(2 rows)


bench=#  SELECT pg_relation_size('pgbench_accounts');
  pg_relation_size
------------------
       13429514240
(1 row)

bench=# SELECT count(*) FROM pgbench_accounts;
    count
-----------
  100000000
(1 row)

Time: 118884.277 ms


So doing the math seq read speed is about 110MB/s (i.e 13 GB in 120
sec). Sure enough, while I was running the query iostat showed:

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await r_await w_await  svctm  %util
sda               0.00     0.00  926.00    0.00 114.89     0.00
254.10     1.90    2.03    2.03    0.00   1.08 100.00


So might be useful for us to see something like that from your system -
note you need to check you really have flushed the cache, and that no
other apps are using the db.

regards

Mark

On 12/07/17 00:46, Charles Nadeau wrote:
> 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.
>