Postgres 14

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?.

version
PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit

WITH test (acquired_at_cleaned, founded_at_clean, n) AS (
        SELECT current_date, current_date - INTERVAL '4' YEAR, 4 UNION
        SELECT current_date, current_date - INTERVAL '3' YEAR, 3 UNION
        SELECT current_date, current_date - INTERVAL '2' YEAR, 2 UNION
        SELECT current_date, current_date - INTERVAL '1' YEAR, 1
     )
   , cases AS (
        SELECT test.*
             , CASE WHEN acquired_at_cleaned <= founded_at_clean::timestamp + INTERVAL '3' year
                    THEN 1 ELSE NULL
                END AS acquired_3_yrs_case1
             , CASE WHEN acquired_at_cleaned - founded_at_clean::timestamp <= '3 year'
                    THEN 1 ELSE NULL
                END AS acquired_3_yrs_case2
             , acquired_at_cleaned - founded_at_clean::timestamp AS x1
             , acquired_at_cleaned - (n * INTERVAL '1' YEAR) AS x2
          FROM test
     )
SELECT acquired_at_cleaned AS acquired
     , founded_at_clean    AS founded
     , n
     , acquired_3_yrs_case1 AS case1
     , acquired_3_yrs_case2 AS case2
     , x1, x2
  FROM cases
 ORDER BY founded_at_clean
;

acquired	founded	n	case1	case2	x1	x2
2021-12-26	2017-12-26 00:00:00	4	null	null	1461 days	2017-12-26 00:00:00
2021-12-26	2018-12-26 00:00:00	3	1	null	1096 days	2018-12-26 00:00:00
2021-12-26	2019-12-26 00:00:00	2	1	1	731 days	2019-12-26 00:00:00
2021-12-26	2020-12-26 00:00:00	1	1	1	365 days	2020-12-26 00:00:00