RE: effective_io_concurrency --- any idea how this should be tuned for SSD or PCIe flash? The manual only gives an example of how to tune for RAID (by number of drives).
From my experience, for each SSD drive you can set it between 5-8 (if there are in raid0).
BTW in 9.6 the effective_io_concurrency [0] can be different for each table space (especially useful if you have multiple drives of a varying quality [ssd, spinning] in one server).
[0] https://www.postgresql.org/docs/9.6/static/runtime-config-re...
On modern SSD, values 5-8 per device are definitely on the low side. See for example this thread from 2014, on (then released S3500 SSD from Intel):
https://www.postgresql.org/message-id/CAHyXU0yiVvfQAnR9cyH%3...
That shows the performance increases up to effective_io_concurrency=256 (of course, in reality there may be multiple queries running on the system, sharing the I/O paths).
I did some testing a while ago and didn't see much gain past 30 on 4 SSD disks. Should do another round of testing. Thank you for sharing.
I'm curious as to what the performance looks like when it's properly tuned.
You should try set track_io_timing = on before running the query. That will show you the actual amount of time spent on i/o. Additionally, if you are using RAID, and haven't tuned it yet, you should try increasing the effective_io_concurrency parameter.