Postgres 12

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 t(id int, status varchar(20),status_date timestamp)

CREATE TABLE


insert into t 
select 123,'appointment',cast('2018-01-09 13:18:09' as timestamp) union all
select 123,'called'     ,cast('2018-01-09 13:37:52' as timestamp) union all
select 123,'left'       ,cast('2018-09-20 14:51:13' as timestamp) union all
select 547,'appointment',cast('2018-02-14 09:43:15' as timestamp) union all
select 547,'called'     ,cast('2018-02-14 09:43:55' as timestamp) union all
select 547,'call back'  ,cast('2018-03-14 11:49:58' as timestamp) union all
select 547,'called'     ,cast('2018-04-25 12:48:44' as timestamp) union all
select 547,'sold'       ,cast('2018-06-06 07:36:56' as timestamp) 

INSERT 0 8


select distinct 
       id
      ,first_value(status) over(partition by id order by status_date) as first_status
      ,first_value(status_date) over(partition by id order by status_date) as first_status_date
      ,last_value(status) over(partition by id order by status_date      range between unbounded preceding and unbounded following) as last_status
      ,last_value(status_date) over(partition by id order by status_date range between unbounded preceding and unbounded following) as last_status_date
  from t      


id
first_status
first_status_date
last_status
last_status_date


547
appointment
2018-02-14 09:43:15
sold
2018-06-06 07:36:56

123
appointment
2018-01-09 13:18:09
left
2018-09-20 14:51:13



SELECT 2

id	first_status	first_status_date	last_status	last_status_date
547	appointment	2018-02-14 09:43:15	sold	2018-06-06 07:36:56
123	appointment	2018-01-09 13:18:09	left	2018-09-20 14:51:13