SQL Postgres query very slow with multiple tables-CodePudding

I am using Postgres.

Schema

 ------------    -----------    --------------------- 
| employee   |  | contact   |  | powwowpersalmapping |
 ------------    -----------    --------------------- 
| member_id  |  | member_id |  | id_number           |
| staff_code |  | idnumber  |  | persal_number       |
 ------------    -----------    ---------------------

Data

Goal

As you can see, the staff_code is blank, so I am trying to set the staff_code on the employee table with the persal_number on the powwowpersalmapping table.

Question

How do I construct the UPDATE query to copy the persal_number to the staff_code?

Problem

I am creating an UPDATE query, but the equivalent SELECT query is very slow, so I think the UPDATE query will be slow too.

SQL

I have the following:

If I run this SELECT query with table joins. it runs pretty fast.

SELECT e.* FROM employee e
INNER JOIN contact c ON c.member_id = e.member_id 
INNER JOIN powwowpersalmapping m ON m.id_number = c.idnumber
WHERE e.staff_code is null or coalesce(e.staff_code, '') = '';

Then I run this SELECT query with multiple tables (no joins). it runs very slow.

SELECT e.* FROM employee e
, contact c, powwowpersalmapping m 
WHERE c.member_id = e.member_id 
AND m.id_number = c.idnumber
AND e.staff_code is null or coalesce(e.staff_code, '') = '';

So I am constructing an UPDATE query (not run yet), and have the following so far, but I am sure it will also be very slow.

UPDATE employee e
SET e.staff_code = m.persal_number
FROM contact c, powwowpersalmapping m 
WHERE c.member_id = e.member_id 
AND m.id_number = c.idnumber
AND e.staff_code is null or coalesce(e.staff_code, '') = '';

How about the following?

UPDATE employee e
SET e.staff_code = (
    SELECT m.persal_number FROM employee e
    INNER JOIN contact c ON c.member_id = e.member_id 
    INNER JOIN powwowpersalmapping m ON m.id_number = c.idnumber
    WHERE e.staff_code is null or coalesce(e.staff_code, '') = ''
);

CodePudding user response：

You can use joins in the UPDATE statement after the FROM clause:

UPDATE employee e
SET staff_code = m.persal_number
FROM contact c INNER JOIN powwowpersalmapping m 
ON m.id_number = c.idnumber
WHERE c.member_id = e.member_id AND COALESCE(e.staff_code, '') = '';

Since the SELECT query runs fast (I believe there are indexes for all the related columns) this will also run fast.
If you could also get rid of the COALESCE() function it would be even faster.
Is the column staff_code empty? If yes, then remove AND COALESCE(e.staff_code, '') = ''.