Postgres 11

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,  insert data

create table stack_question
(
	effective_from timestamp,
	archived timestamp,
	cal double precision
);

INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-12 00:00:00.000000', '2020-01-13 00:00:00.000000', 3.7);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-14 00:00:00.000000', 4.2);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-14 00:00:00.000000', '2020-01-15 00:00:00.000000', 7.2);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-15 00:00:00.000000', 8.6);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-15 00:00:00.000000', '2020-01-16 00:00:00.000000', 7.2);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-16 00:00:00.000000', '2020-01-17 00:00:00.000000', 11.2);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-17 00:00:00.000000', '2020-01-18 00:00:00.000000', 13.6);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-13 00:00:00.000000', '2020-01-19 00:00:00.000000', 42.2);
INSERT INTO public.stack_question (effective_from, archived, cal) VALUES ('2020-01-19 00:00:00.000000', '2020-01-20 00:00:00.000000', 12.3);

--Data were're dealing with
SELECT effective_from, archived, cal FROM stack_question ORDER BY archived ASC;

--Intended result: A query that returns a table with only the rows that don't overlap in time.
--In case of a conflict, use the most recently archived row.

--Can be achieved manually by:

(SELECT * FROM stack_question WHERE effective_from <= '12-Jan-2020' ORDER BY archived DESC LIMIT 1) --First 'effective_from' value from above select
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1) --Second 'effective_from'
UNION
(SELECT * FROM stack_question WHERE effective_from <= '14-Jan-2020' ORDER BY archived DESC LIMIT 1) --And so on..
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '15-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '16-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '17-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '13-Jan-2020' ORDER BY archived DESC LIMIT 1)
UNION
(SELECT * FROM stack_question WHERE effective_from <= '19-Jan-2020' ORDER BY archived DESC LIMIT 1) ORDER BY effective_from ASC;

--So the first row covers the time period Jan-12 to 13 uniquely.
--The following 6 rows are all superceded by row 8, which covers the time period Jan-13 to Jan-19,
--so disregard them in the final output.

--Row 9 covers the unique time period Jan-19 to 20.

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

effective_from	archived	cal
2020-01-12 00:00:00	2020-01-13 00:00:00	3.7
2020-01-13 00:00:00	2020-01-14 00:00:00	4.2
2020-01-14 00:00:00	2020-01-15 00:00:00	7.2
2020-01-13 00:00:00	2020-01-15 00:00:00	8.6
2020-01-15 00:00:00	2020-01-16 00:00:00	7.2
2020-01-16 00:00:00	2020-01-17 00:00:00	11.2
2020-01-17 00:00:00	2020-01-18 00:00:00	13.6
2020-01-13 00:00:00	2020-01-19 00:00:00	42.2
2020-01-19 00:00:00	2020-01-20 00:00:00	12.3

effective_from	archived	cal
2020-01-12 00:00:00	2020-01-13 00:00:00	3.7
2020-01-13 00:00:00	2020-01-19 00:00:00	42.2
2020-01-19 00:00:00	2020-01-20 00:00:00	12.3