I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to "Account" table.
My CREATE TABLE statement looks like this:
CREATE TABLE "NewTable" ( "column1" VARCHAR(100) NOT NULL, "column2" INTEGER NOT NULL, PRIMARY KEY("column1") CONSTRAINT "SomeFK" FOREIGN KEY ("column2") REFERENCES "Account" ("ID") )
The above statement executed for more than 25 minutes and didn't complete. We killed the transaction.
When we remove the foreign key constraint, it executes immediately.
Can someone let us know what is the problem? We initially thought it was locking the "Account" table for some reason, but when we looked at "pg_locks", we did not find any entry for "Account" - only an exclusive lock for "NewTable".
NULLs, is it still slow?