Supervisors and employees query, with a subselect and inner join

Question

I want to replace or improve the SELECT command of tblVisor to make it faster.

Is there any way to improve this SQL command?

SELECT tblVisor.supervisor_id,
       tblVisor.last_name,
       tblVisor.first_names,
       tblVisor.employee_job_profile_id,
       org_employees.last_name,
       org_employees.first_name,
       org_employees.job_code
FROM (SELECT cp_supervisor_properties.supervisor_id,
             persons.last_name,
             persons.first_names,
             cp_supervisor_properties.employee_job_profile_id
      FROM cp_supervisor_properties
      INNER JOIN persons ON persons.person_id = cp_supervisor_properties.supervisor_id) as tblVisor
INNER JOIN org_employees ON org_employees.employee_number = tblVisor.employee_job_profile_id
LIMIT 100

Answer 1

Unless I'm missing something there doesn't seem to be a reason for the subselect, just join all three tables directly. Also, INNER is the default, so you could drop that prefix as well.

The query would then become something like the following:

SELECT cp_supervisor_properties.supervisor_id,
       persons.last_name,
       persons.first_names,
       cp_supervisor_properties.employee_job_profile_id,
       org_employees.last_name,
       org_employees.first_name,
       org_employees.job_code
FROM cp_supervisor_properties
INNER JOIN persons
  ON persons.person_id = cp_supervisor_properties.supervisor_id
INNER JOIN org_employees
  ON org_employees.employee_number = cp_supervisor_properties.employee_job_profile_id
LIMIT 100;

Without more information (about how the data looks and so) I don't see possible performance improvements. If you have problems with the query time you should probably check for missing indexes on the join columns and in general look into query optimisation.

Answer 2

You could greatly improve the readability of your query by using a Common Table Expression instead of a sub-query. Then just refer to the CTE like you would a table. Like so:

WITH tblVisor AS (
    SELECT cp_supervisor_properties.supervisor_id,
           persons.last_name,
           persons.first_names,
           cp_supervisor_properties.employee_job_profile_id
    FROM cp_supervisor_properties
      INNER JOIN persons 
        ON persons.person_id = cp_supervisor_properties.supervisor_id
)
SELECT tblVisor.supervisor_id,
       tblVisor.last_name,
       tblVisor.first_names,
       tblVisor.employee_job_profile_id,
       org_employees.last_name,
       org_employees.first_name,
       org_employees.job_code
FROM tblVisor
  INNER JOIN org_employees 
    ON org_employees.employee_number = tblVisor.employee_job_profile_id
LIMIT 100

This likely will not change performance much or at all, but it is easier to maintain. If this query is called often, perhaps consider writing a SQL function to store the execution plan with it, such as:

CREATE OR REPLACE FUNCTION my_function_name()
LANGUAGE plpgsql AS $$
BEGIN
    WITH tblVisor AS (
        SELECT cp_supervisor_properties.supervisor_id,
               persons.last_name,
               persons.first_names,
               cp_supervisor_properties.employee_job_profile_id
        FROM cp_supervisor_properties
          INNER JOIN persons 
            ON persons.person_id = cp_supervisor_properties.supervisor_id
    )
    SELECT tblVisor.supervisor_id,
           tblVisor.last_name,
           tblVisor.first_names,
           tblVisor.employee_job_profile_id,
           org_employees.last_name,
           org_employees.first_name,
           org_employees.job_code
    FROM tblVisor
      INNER JOIN org_employees 
        ON org_employees.employee_number = tblVisor.employee_job_profile_id
    LIMIT 100;
END; $$

Then whenever you need to run it:

SELECT my_function_name();

That should speed it up after you run it once, since it will store the execution plan with it instead of having to calculate it each time.