The last post was too long. I think I have a much more simpler example that is easier to replicate.
postgres=# create table lowc_test (id bigserial, int_data int, primary key(id));
CREATE TABLE
postgres=# \timing
Timing is on.
postgres=# truncate lowc_test;
postgres=# show synchronous_commit;
synchronous_commit
--------------------
off
(1 row)
Time: 0.445 ms
postgres=# show fsync;
fsync
-------
on
(1 row)
Time: 0.331 ms
postgres=# -- no secondary index
postgres=#
postgres=# insert into lowc_test (int_data) select s from generate_series(1,5000000) s;
INSERT 0 5000000
Time: 12440.497 ms (00:12.440)
postgres=# truncate lowc_test;
TRUNCATE TABLE
Time: 68.427 ms
postgres=# create index on lowc_test using btree (int_data);
CREATE INDEX
Time: 2.449 ms
postgres=# -- 2ndary index w/ normal sequence of values inserted
postgres=#
postgres=# insert into lowc_test (int_data) select s from generate_series(1,5000000) s;
INSERT 0 5000000
Time: 17221.095 ms (00:17.221)
postgres=# -- 2ndary index w/ single value inserted
postgres=#
postgres=# truncate lowc_test;
TRUNCATE TABLE
Time: 83.846 ms
postgres=# insert into lowc_test (int_data) select 42 from generate_series(1,5000000) s;
INSERT 0 5000000
Time: 21440.356 ms (00:21.440)
