How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql 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 &lt; 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:


How can we avoid the GROUP BY operation to occurs over the complete
addresses table by using LIMIT OFFSET of the outer query?
Will the condition WHERE employee.employee_id &lt; 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 &lt; 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 &lt; 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