How to create index parallelly on two columns in postgres

Question

I wanted to create two index on my table having 500 million rows, Since index creation will take some time to finish, I am thinking to run two indexes statement parallelly, But I don't know how, I want to use procedure to create the Index, Here is my function:

CREATE OR REPLACE PROCEDURE test() LANGUAGE plpgsql AS $PROCEDURE$
BEGIN
   SET statement_timeout = 7200000;
   COMMIT;
   CREATE INDEX IF NOT EXISTS idx_tt1_org_id ON temp_table_1(org_id);
   CREATE INDEX IF NOT EXISTS idx_tt1_input_id ON temp_table_1(input_id);
END;$PROCEDURE$;

Please help me how to run these index statement in parallel. Thanks

Unrelated to your problem, but: Postgres 9.1 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Dec 10, 2021 at 10:27
You mean I can create two procedure and move one create index statement in one procedure and other in other procedure and change the create index query to CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tt1_org_id ON temp_table_1(org_id); — Braham Shakti
– Braham Shakti, Commented Dec 10, 2021 at 11:22
Essentially yes. But if your system is I/O bound (which is highly likely) then running two CREATE INDEX statements in parallel, will most likely make things slower, not faster. — user330315
– user330315, Commented Dec 10, 2021 at 11:48
@a_horse_with_no_name A normal index creation only takes a Share lock, which does not block other simultaneous ordinary index creations. (But does block out INSERT/UPDATE/DELETE). While creating an index CONCURRENTLY will block other CONCURRENTLY indexes, but won't block INSERT/UPDATE/DELETE. — jjanes
– jjanes, Commented Dec 10, 2021 at 17:13

jjanes · Accepted Answer · 2021-12-10 17:20:33Z

2

You would need to open two sessions and create one index in each session. So you can't do it from one procedure. (You might be able to get around that with tricky uses of dblink or something to have a procedure open other connections).

Modern database versions (not the long-EOL 9.1) will automatically parallelize individual btree index creations, so there may not be much point in trying to create multiple at the same time if each one is already happening in parallel.

answered Dec 10, 2021 at 17:20

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create index parallelly on two columns in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related