On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote: > >> Did not see consistent measurable gains > 256 > >> effective_io_concurrency. Interesting that at setting of '2' (the > >> lowest possible setting with the feature actually working) is > >> pessimal. > > > > Very interesting. When we added a per-tablespace random_page_cost, > > there was a suggestion that we might want to add per-tablespace > > effective_io_concurrency someday: > > What I'd really like to see is to have effective_io_concurrency work > on other types of scans. It's clearly a barn burner on fast storage > and perhaps the default should be something other than '1'. Spinning > storage is clearly dead and ssd seem to really benefit from the posix > readhead api.
I haven't played much with SSD, but effective_io_concurrency can be a big win even on spinning disk.
Well, the real question is knowing which blocks to request before actually needing them. With a bitmap scan, that is easy --- I am unclear how to do it for other scans. We already have kernel read-ahead for sequential scans, and any index scan that hits multiple rows will probably already be using a bitmap heap scan.
If the index scan is used to provide ordering as well as selectivity than it will resist being converted to an bitmap scan. Also it won't convert to a bitmap scan solely to get credit for the use of effective_io_concurrency, as that setting doesn't enter into planning decisions.
For a regular index scan, it should be easy to prefetch table blocks for all the tuples that will need to be retrieved based on the current index leaf page, for example. Looking ahead across leaf page boundaries would be harder.