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 cte as
select '2019-11-12 16:01:55' as timestamp, null as owner_id, null as owner_assigneddate, null as lastmodifieddate union all
select '2019-11-12 19:03:18' as timestamp, 39530934 as owner_id, '2019-11-12 19:03:18' as owner_assigneddate, '2019-11-12 19:03:18' as lastmodifieddate union all
select '2019-11-12 19:03:19' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:19' as lastmodifieddate union all
select '2019-11-12 19:03:20' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:20' as lastmodifieddate union all
select '2019-11-12 19:03:31' as timestamp, 40320368 as owner_id, '2019-11-12 19:03:31' as owner_assigneddate, '2019-11-12 19:03:31' as lastmodifieddate union all
select '2019-11-12 19:03:33' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:33' as lastmodifieddate union all
select '2019-11-12 19:03:56' as timestamp, null as owner_id, null as owner_assigneddate, '2019-11-12 19:03:356' as lastmodifieddate

SELECT 7


select
    cte.*,
    max(case when owner_id is not null then timestamp end) 
	over(order by timestamp rows unbounded preceding) target_timestamp
from cte
order by timestamp


timestamp
owner_id
owner_assigneddate
lastmodifieddate
target_timestamp


2019-11-12 16:01:55
null
null
null
null

2019-11-12 19:03:18
39530934
2019-11-12 19:03:18
2019-11-12 19:03:18
2019-11-12 19:03:18

2019-11-12 19:03:19
null
null
2019-11-12 19:03:19
2019-11-12 19:03:18

2019-11-12 19:03:20
null
null
2019-11-12 19:03:20
2019-11-12 19:03:18

2019-11-12 19:03:31
40320368
2019-11-12 19:03:31
2019-11-12 19:03:31
2019-11-12 19:03:31

2019-11-12 19:03:33
null
null
2019-11-12 19:03:33
2019-11-12 19:03:31

2019-11-12 19:03:56
null
null
2019-11-12 19:03:356
2019-11-12 19:03:31



SELECT 7


select 
    c.timestamp,
    coalesce(c.owner_id, c_lag.owner_id) owner_id,
    c.owner_assigneddate,
    c.lastmodifieddate
from 
	(
		select
			cte.*,
			max(case when owner_id is not null then timestamp end) 
				over(order by timestamp rows unbounded preceding) target_timestamp
		from cte
	) c
	left join cte c_lag 
		on c.owner_id is null 
		and c_lag.timestamp = c.target_timestamp


timestamp
owner_id
owner_assigneddate
lastmodifieddate


2019-11-12 16:01:55
null
null
null

2019-11-12 19:03:18
39530934
2019-11-12 19:03:18
2019-11-12 19:03:18

2019-11-12 19:03:19
39530934
null
2019-11-12 19:03:19

2019-11-12 19:03:20
39530934
null
2019-11-12 19:03:20

2019-11-12 19:03:31
40320368
2019-11-12 19:03:31
2019-11-12 19:03:31

2019-11-12 19:03:33
40320368
null
2019-11-12 19:03:33

2019-11-12 19:03:56
40320368
null
2019-11-12 19:03:356



SELECT 7

timestamp	owner_id	owner_assigneddate	lastmodifieddate	target_timestamp
2019-11-12 16:01:55	null	null	null	null
2019-11-12 19:03:18	39530934	2019-11-12 19:03:18	2019-11-12 19:03:18	2019-11-12 19:03:18
2019-11-12 19:03:19	null	null	2019-11-12 19:03:19	2019-11-12 19:03:18
2019-11-12 19:03:20	null	null	2019-11-12 19:03:20	2019-11-12 19:03:18
2019-11-12 19:03:31	40320368	2019-11-12 19:03:31	2019-11-12 19:03:31	2019-11-12 19:03:31
2019-11-12 19:03:33	null	null	2019-11-12 19:03:33	2019-11-12 19:03:31
2019-11-12 19:03:56	null	null	2019-11-12 19:03:356	2019-11-12 19:03:31