CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Braham Shakti
Braham Shakti

Reputation: 1456

How to create index parallelly on two columns in postgres

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

Upvotes: 1

Views: 1837

Answers (1)

jjanes
jjanes

Reputation: 44137

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.

Upvotes: 2

Related Questions