Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

From: Denisa Cirstescu <Denisa(dot)Cirstescu(at)tangoe(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL
Date: 2019-01-22 16:29:48
Message-ID: MWHPR12MB1664CD85ECAB0420396FF210E6980@MWHPR12MB1664.namprd12.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I am trying to add a new column to a really big table and to define an INDEX and a FOREIGN KEY on that new column using the following instructions:

ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER;
CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId);
ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(DepartmentId) REFERENCES Department(DepartmentId);

The table is huge and it takes a lot of time to add the INDEX and the FOREIGN KEY although all values are NULL.
Considering that the new DepartmentId column is NULL for all rows at this point, is there a way to make the INDEX and FOREIGN KEY creation run faster?

See below information about the size of the table and also the size of its associated Primary Key and Indexes:
Employee 339 GB
Employee_PKEY 46 GB
IDX_Employee_JobId 46 GB
IDX_Employee_IsWFH 46 GB

Thank you,
Denisa Cîrstescu

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2019-01-22 16:38:46 Re: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL
Previous Message Achilleas Mantzios 2019-01-22 16:17:18 Re: PostgreSQL logical replication depends on WAL segments?