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 tab
AS
SELECT 1 AS ID_1, 'a' AS name, '2018-01-01' d, 2 AS ID_2 UNION ALL
SELECT 2, 'a', '2018-01-01', 1 UNION ALL
SELECT 1, 'b', '2018-01-02', 3

SELECT 3


CREATE TABLE only_one_to_one
AS
SELECT ID_1, Name, D, ID_2
FROM (SELECT *,COUNT(*) OVER(PARTITION BY ID_1) AS ID1_cnt,
               COUNT(*) OVER(PARTITION BY ID_2) AS ID2_cnt
      FROM tab) sub
WHERE ID1_cnt = 1 AND ID2_cnt = 1;

SELECT 1


SELECT * FROM tab


id_1
name
d
id_2


1
a
2018-01-01
2

2
a
2018-01-01
1

1
b
2018-01-02
3



SELECT 3


SELECT * FROM  only_one_to_one


id_1
name
d
id_2


2
a
2018-01-01
1



SELECT 1