Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE Employees(Employee_ID int,     From_Date date,   To_Date date);
INSERT INTO Employees(Employee_ID,From_Date,To_Date)
  VALUES
  (1    ,       '1998-01-02'   ,  NULL),
  (1    ,       '2000-01-01'   ,  NULL),
  (1    ,       '2015-01-01'   ,  '2018-01-01'),
  (2    ,       '2005-01-01'   ,  '2006-01-01'),
    (2    ,       '2005-01-01'   ,  '2006-01-01'),
  (2    ,       '2007-01-01'   ,  '2007-05-05'),
  (3    ,       '2002-02-02'   ,  '2004-02-02'),
  (3    ,       '2010-01-01'   ,  '2015-01-01'),
  (4    ,       '2001-01-01'   ,  '2005-03-03'),
  (4    ,       '2003-03-03'   ,  '2004-04-04')
10 rows affected

SELECT Employee_ID,min(from_date) as from_date
FROM Employees
WHERE to_date IS NOT NULL 
GROUP BY Employee_ID


employee_id
from_date


3
2002-02-02

4
2001-01-01

2
2005-01-01

1
2015-01-01


SELECT Employee_ID,from_Date,To_Date,ROW_NUMBER() OVER(PARTITION BY Employee_ID order by from_Date asc,To_Date asc ) as rownum
FROM Employees
WHERE to_date IS NOT NULL


employee_id
from_date
to_date
rownum


1
2015-01-01
2018-01-01
1

2
2005-01-01
2006-01-01
1

2
2005-01-01
2006-01-01
2

2
2007-01-01
2007-05-05
3

3
2002-02-02
2004-02-02
1

3
2010-01-01
2015-01-01
2

4
2001-01-01
2005-03-03
1

4
2003-03-03
2004-04-04
2


WITH CTE
AS
( 
SELECT Employee_ID,min(from_date) as from_date
FROM Employees
GROUP BY Employee_ID
)

SELECT  C.Employee_ID,C.from_Date,MIN(E.To_Date)
FROM CTE C
INNER JOIN Employees E
on E.Employee_ID = C.Employee_ID AND C.from_Date = E.from_Date
WHERE DATE_PART('year', E.To_Date::date) - DATE_PART('year',C.From_Date::date) < 3
AND  to_date IS NOT NULL 
GROUP BY  C.Employee_ID,C.from_Date
ORDER BY C.Employee_ID;



employee_id
from_date
min


2
2005-01-01
2006-01-01

3
2002-02-02
2004-02-02


WITH CTE
AS
(
SELECT Employee_ID,from_Date,To_Date,ROW_NUMBER() OVER(PARTITION BY Employee_ID order by from_Date asc,To_Date asc ) as rownum
FROM Employees

)
SELECT Employee_ID,from_Date,To_Date
FROM CTE C
WHERE DATE_PART('year', To_Date::date) - DATE_PART('year',From_Date::date) < 3
AND  to_date IS NOT NULL
AND rownum = 1
ORDER BY C.Employee_ID;




employee_id
from_date
to_date


2
2005-01-01
2006-01-01

3
2002-02-02
2004-02-02

employee_id	from_date	to_date	rownum
1	2015-01-01	2018-01-01	1
2	2005-01-01	2006-01-01	1
2	2005-01-01	2006-01-01	2
2	2007-01-01	2007-05-05	3
3	2002-02-02	2004-02-02	1
3	2010-01-01	2015-01-01	2
4	2001-01-01	2005-03-03	1
4	2003-03-03	2004-04-04	2