Re: Index scan prefetch?

On Mon, Mar 26, 2018 at 4:59 PM, Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>> But now effective_io_concurrency parameter is applicable only for bitmap
> ...
>> Will it be useful to support it also for index scan?
>> Or there are some other ways to address this problem?
>
> Does your case perform well with bitmap heap scan (I mean bitmap scan of the
> single index)? It seems to me that prefetch wouldn't help, as it would just
> incur the same random cost you're already seeing; the solution may be to choose
> another plan(bitmap) with sequential access to enable read-ahead,
>
> Also: Claudio mentioned here that bitmap prefetch can cause the kernel to avoid
> its own readahead, negatively affecting some queries:
> https://www.postgresql.org/message-id/flat/8fb758a1-d7fa-4dcc-fb5b-07a992ae6a32%40gmail(dot)com#20180207054227(dot)GE17521(at)telsasoft(dot)com
>
> What's the pg_stats "correlation" for the table column with index being
> scanned? How many tuples? Would you send explain(analyze,buffers) for the
> problem query, and with SET enable_bitmapscan=off; ?

Also, check out this thread:

http://www.postgresql-archive.org/Prefetch-index-pages-for-B-Tree-index-scans-td5728926.html