On 10/12/2014 17:52, Jeff Janes wrote:
> On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce(at)momjian(dot)us
> <mailto:bruce(at)momjian(dot)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.
>
I also think that having effective_io_concurrency for other nodes that
bitmap scan would be really great, but for now
having a per-tablespace effective_io_concurrency is simpler to implement
and will already help, so here's a patch to implement it. I'm also
adding it to the next commitfest.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org