tambykojak tambykojak - 29 days ago 5
SQL Question

Update table from sub query is performing really slow

I am having a tough time solving a SQL problem. I have scoured the internet already and failed to find a solution.

I've provided some context below. I've only included what I think is relevant.

Existing Tables:

Group
id - has index

Admin
id - has index

Post
id - has index
group_id - has index
admin_id - has index


My Problem:

There used to be a bug in the software that was not populating the
admin_id
column when a post is created. This was okay because up until now it was assumed that every group would only have one admin.

We are now adding functionality to have multiple admins for each group, so
admin_id
needs to be populated. The bug in the software has been fixed, but we have to go back and populate the
admin_id
for each post.

My Solution/Question:

What am I doing wrong? Below is my query. It literally takes 45 minutes to run. I cannot find a way to fix this. I am okay with it taking 45 minutes if it needs to, but I really hate it and I am going crazy over finding an efficient way to do this with SQL.

UPDATE posts
SET admin_id = X.admin_id
FROM (
SELECT
posts.group_id,
admins_groups.admin_id
FROM posts
JOIN groups ON posts.group_id = groups.id
JOIN admins_groups ON groups.id = admins_groups.group_id
) AS X
WHERE posts.group_id = X.group_id;


What I have tried so far.

Besides scouring the internet and failing...

I read that indexes make writes slower, so I have removed the index on posts.admin_id and have seen a 20% performance increase, but that's still now good enough.

Answer

As far as I can tell, you don't need the groups table and you should not repeat the target table of an update in the FROM clause:

UPDATE posts 
    SET admin_id = admins_groups.admin_id 
FROM admin_groups 
WHERE posts.group_id = admins_groups.group_id