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

WITH product_group AS (
    SELECT 1 AS id, 'Nice' AS group_name UNION ALL
    SELECT 2, 'Very Nice'
),
product AS (
    SELECT 1 AS prod_id, 'something' AS name, 2 AS group_id UNION ALL
    SELECT 2, 'psp3', 1 UNION ALL
    SELECT 3, 'bundle1', 2 UNION ALL
    SELECT 4, 'bundle2', 1
),
version AS (
    SELECT 1 AS id, 2 AS prod_id, 1.0 AS version UNION ALL
    SELECT 2, 2, 1.1 UNION ALL
    SELECT 3, 3, 2.3 UNION ALL
    SELECT 4, 1, 0.1 UNION ALL
    SELECT 5, 4, 0.4 UNION ALL
    SELECT 6, 1, 0.2
),
product_group_child as (
SELECT 1 AS pgt_child_id,             2 AS group_id,           'root2' as default_something UNION ALL
SELECT 2,             1,            'root1' 
),
product_child as (
SELECT 1 AS pt_child_id,             2 AS prod_id,           'override2' as default_something 
),
version_child as (
SELECT 1 AS vt_child_id,             3 AS version_id,           'winner' as default_something 
),
pivot as (
SELECT 1 AS p_id, 3 AS version_id, 2 as prod_id UNION ALL
SELECT 2, 3, 1 UNION ALL
SELECT 3, 5, 1
)

SELECT DISTINCT ON (pg.id, p.prod_id)
    pg.group_name, p.name AS prod_name, v.version,
COALESCE((select default_something from version_child where version_id = v.id),
(select default_something from product_child where prod_id = p.prod_id),
(select default_something from product_group_child where group_id = pg.id)
) as something
FROM product_group pg
LEFT JOIN product p ON pg.id = p.group_id
LEFT JOIN version v ON v.prod_id = p.prod_id
ORDER BY pg.id, p.prod_id, v.version DESC;

group_name	prod_name	version	something
Nice	psp3	1.1	override2
Nice	bundle2	0.4	root1
Very Nice	something	0.2	root2
Very Nice	bundle1	2.3	winner