Ygthkb

I have legacy tables similar to the following:

employee

------------------------------------

|  employee_id  |   name    

------------------------------------

|  1            |   David    

|  2            |  Mathew  

------------------------------------

payroll

-------------------------------------

|  employee_id  | salary      

-------------------------------------

|   2           |  200000    

|   3           |  90000      

-------------------------------------

I want to get the following data, after joins and filters:

-----------------------------------------------------------

|  address_id  | employee_id  |  address        

-----------------------------------------------------------

|   1          |  2           |  street 1, NY  

|   2          |  2           |  street 2, DC  

------------------------------------------------------------

I have the following query:

SELECT employee_id, salary, address_arr 

FROM employee 

LEFT JOIN payroll on payroll.employee_id = employee.employee_id

INNER JOIN 

     (

       SELECT employee_id, ARRAY_AGG(address) as address_arr 

       FROM addresses 

       GROUP BY employee_id

     ) table_address ON table_address.employee_id = employee.employee_id

WHERE employee.employee_id < 1000000

LIMIT 100

OFFSET 0

Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.

Kindly answer:

1. How can we avoid the GROUP BY operation to occurs over the complete
   addresses table by using LIMIT OFFSET of the outer query?


2. Will the condition WHERE employee.employee_id < 1000000 be applied on subquery before or after the GROUP BY operation in the inner query. If the condition is applied after the GROUP BY, how can we avoid that?

Note: There are multiple JOINs and subqueries in the actual query being used.

I am not sure if this is really more efficient, but you could try to join to a derived table that applies the limit.

select emp.employee_id, emp.salary, adr.address_arr 

from (

  SELECT employee_id, salary, address_arr 

  FROM employee 

    LEFT JOIN payroll on payroll.employee_id = employee.employee_id

  WHERE employee.employee_id < 1000000

  LIMIT 100

  OFFSET 0

) as emp

  JOIN (

     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr 

     FROM addresses a

     GROUP BY employee_id

  ) as adr ON adr.employee_id = emp.employee_id;

The first derived table only selects 100 rows, and the join/group by should then only be done for those 100 employees.

If the optimizer doesn't push that down, you could try a lateral join instead to "force" a push down:

select emp.employee_id, emp.salary, adr.address_arr 

from (

  SELECT employee_id, salary, address_arr 

  FROM employee 

    LEFT JOIN payroll on payroll.employee_id = employee.employee_id

  WHERE employee.employee_id < 1000000

  LIMIT 100

  OFFSET 0

) as emp

  LATERAL JOIN (

     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr 

     FROM addresses a

     WHERE a.employee_id = emp.employee_id

     GROUP BY employee_id

  ) as adr ON adr.employee_id = emp.employee_id;

The join condition isn't really needed, but it dosn't hurt either

Maybe

SELECT employee.employee_id, payroll.salary, ARRAY_AGG(addresses.address)

FROM employee 

INNER JOIN addresses ON addresses.employee_id = employee.employee_id

LEFT JOIN payroll on payroll.employee_id = employee.employee_id

WHERE employee.employee_id < 1000000

GROUP BY employee.employee_id

LIMIT 100

OFFSET 0

?

And - do you really need in records where no appropriate records in payroll table which leads to NULLs in payroll.salary? Maybe, INNER JOIN is enough?

I am new to this and had some help , here's what I came up with:

with t as (SELECT employee.employee_id, salary  FROM employee  LEFT JOIN payroll on payroll.employee_id = employee.employee_id WHERE employee.employee_id < 1000000 LIMIT 100 OFFSET 0)

select t.employee_id, max(t.salary), ARRAY_AGG(address) as address_arr from address left join t on address.employee_id = t.employee_id where address.employee_id = t.employee_id group by t.employee_id;

explain analyze yields