Postgres ALTER TABLE... unexpected performance when applying multiple changes-CodePudding

I am attempting to speed up a bulk load. The bulk load is performed into a table with all primary keys, indexes, and foreign keys dropped. After data finishes loading we go and apply the necessary constraints back to the database. As a simple test I have the following setup:

CREATE TABLE users
(
    id int primary key
);

CREATE TABLE events
(
    id serial,
    user_id1 int,
    user_id2 int,
    unique_int1 int,
    unique_int2 int,
    unique_int3 int
);

INSERT INTO
    users (id)
SELECT generate_Series(1,100000000);

INSERT INTO
    events (user_id1,user_id2,unique_int1,unique_int2,unique_int3)
SELECT
    generate_Series(1,100000000),
    generate_Series(1,100000000),
    generate_Series(1,100000000),
    generate_Series(1,100000000),
    generate_Series(1,100000000);

I then wish to apply the following constraints via 5 individual alter table statements:

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_user_id1_fkey FOREIGN KEY (user_id1) REFERENCES public.users(id);
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_user_id2_fkey FOREIGN KEY (user_id2) REFERENCES public.users(id);
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_unique_int1_me_key UNIQUE (unique_int1);
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_unique_int2_me_key UNIQUE (unique_int2);
ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_unique_int3_me_key UNIQUE (unique_int3);

Each of the above statements takes approximately 90 seconds to run for a total of 450 seconds. I expected that when I combined the 5 above statements into a single ALTER TABLE statement that the time would be reduced but in fact it has increased.

alter table only public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id),
    ADD CONSTRAINT events_user_id1_fkey FOREIGN KEY (user_id1) REFERENCES public.users(id),
    ADD CONSTRAINT events_user_id2_fkey FOREIGN KEY (user_id2) REFERENCES public.users(id),
    ADD CONSTRAINT events_unique_int1_me_key UNIQUE (unique_int1),
    ADD CONSTRAINT events_unique_int2_me_key UNIQUE (unique_int2),
    ADD CONSTRAINT events_unique_int3_me_key UNIQUE (unique_int3);

This takes 520 seconds whereas I expected it to at least take less than 450 seconds. My reason for thinking this is from the Postgres documentation for the ALTER TABLE statement where in the Notes section it reads

The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table.

Can anyone explain the above measurements or have any suggestions?

CodePudding user response：

This case is not going to be helped much as each of the commands needs to do a pass to verify the conditions of the constraints FK or UNIQUE. Also the docs:

When multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand.

So by combining the commands you are working on the strictest locking for the entire command.

The section you quoted is amplified further on:

For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large tables, since only one pass over the table need be made.

The conclusion is combining commands is not necessarily a time saver.

CodePudding user response：

Unifying the table read is going to be particularly beneficial if there is no other IO to be done, like column type change or check constraint. But here you need to actually build the indexes or look up the foreign keys, and that is what will dominate your run time. So it is no surprise that you don't see a big gain.

For a small loss in performance, it could be that that is due to worse cache usage, less efficient parallel query execution, or interrupted stride length for readahead or for bulk writing to your IO devices.