On Mon, Mar 26, 2018 at 12:43:02PM +0300, Konstantin Knizhnik wrote: > Hi, hackers. > > I was faced with the following bad performance use case with Postgres: there > is a huge append-only table with serial key (ID) > which is permanently appended using multithreaded pgloader.
I think this could be similar to the problem I reported here: https://www.postgresql.org/message-id/flat/20160524173914.GA11880%40telsasoft.com#20160524173914.ga11...@telsasoft.com The analysis at the time was that, due to "repeated keys", a btree index on the timestamp column had non-consecutive heap TIDs (btree insertion uses random early insertion point to avoid superlinear lookup cost during such insertions). But, our case also involved multiple child processes simultaneously inserting into same table, and I wonder if "repeated keys" were more or less unrelated to the problem. The behavior is maybe caused easily by simultaneous insertions "clustered" around the same target: for us: now(), for you: nextval(). > 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.com#20180207054227.ge17...@telsasoft.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; ? Justin
