Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes

Hi Peter,

The last post was too long. I think I have a much more simpler example that
is easier to replicate.

Cheers,

Paolo

------------------------------
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)
------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.