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(id, group_name) AS (
VALUES (1, 'Nice')
, (2, 'Very Nice')
)
, product(prod_id, name, group_id) AS (
VALUES (1, 'something', 2)
, (2, 'psp3' , 1)
, (3, 'bundle1' , 2)
, (4, 'bundle2' , 1)
)
, version(id, prod_id, version) AS (
VALUES (1, 2, '1.0')
, (2, 2, '1.1')
, (3, 3, '2.3')
, (4, 1, '0.1')
, (5, 4, '0.4')
, (6, 1, '0.2')
)
, product_group_child(pgt_child_id, group_id, default_something) AS (
VALUES (1, 2, 'root2')
, (2, 1, 'root1')
)
, product_child(pt_child_id, prod_id, default_something) AS (
VALUES (1, 2, 'override2')
)
, version_child(vt_child_id, version_id, default_something) AS (
VALUES (1, 3, 'winner')
)
, pivot(p_id, version_id, prod_id) AS (
VALUES (1, 3, 2)
, (2, 3, 1)
, (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)
| group_name | prod_name | version | something | ref |
|---|---|---|---|---|
| Nice | psp3 | 1.1 | override2 | {} |
| Nice | bundle2 | 0.4 | root1 | {something} |
| Very Nice | something | 0.2 | root2 | {} |
| Very Nice | bundle1 | 2.3 | winner | {something,psp3} |
SELECT 4