CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
Marcel
Marcel

Reputation: 4402

Inverting foreign key effectivly with SQL

I have two Tables

Husband
---------
id
name
....

and 

Wife
---------
id
name
husband_id (REFERENCES husband)
...

Now I want to turn around the relation. That means the husband should have a wife_id referencing the wife and the husband_id of the wife should be removed.

Altering the tables is not a deal. But to set the wife_id efficent is. You should see there are a lot of entries.

First I tried the simple way:

UPDATE husband h SET wife_id =
  (SELECT id FROM wife w WHERE w.husband_id=h.id)

It tried first on a small database and realized that the subselect is so slow that the update on the live database would take >4h which is not acceptable.

So, how to solve this problem faster?

Btw. it's a Postgres 9.3.5 database and afaik there are no special indexes set.

Upvotes: 1

Views: 43

Answers (2)

Marcel
Marcel

Reputation: 4402

The solution of a_horse_with_no_name brought some performance. But the real performance push came with a index:

CREATE INDEX husband_id_index ON wife (husband_id);
REINDEX INDEX alt_md_index; -- not sure if this isn't required

The index query itself took only 5 seconds. But the join query was about 30.000 times faster and was done in ~30 seconds. I was pretty suprised and will use indexes from now on more frequently.

Upvotes: 0

user330315
user330315

Reputation:

You can use a JOIN, that should be faster than the co-related sub-query:

UPDATE husband h
   SET wife_id = w.id
FROM wife w 
WHERE w.husband_id = h.id;

Upvotes: 1

Related Questions