Postgres 9.6

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 v_univ_st
(
  NAME_PRODUCT varchar(20),
  PRODUCT_ID varchar(20),
  INGREDIENT_ID varchar(20),
  PRODUCT_ID_COMP varchar(20)
);
                                               
                                               
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('milk',1,'1A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('flour',2,'2A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('egs',3,'3A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('sugar',4,'4A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('salt',5,'5A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('bakingsoda',8,'8A','');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeA',6,'1A','1B');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeA',6,'6A','5A');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeA',6,'6A','1A');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeA',6,'6B','2A');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeB',7,'7A','2A');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeB',7,'7B','8A');
insert into v_univ_st (NAME_PRODUCT,PRODUCT_ID, INGREDIENT_ID,PRODUCT_ID_COMP) values ('cakeB',7,'7C','3A');
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

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM v_univ_st
ORDER BY product_id, ingredient_id;


name_product
product_id
ingredient_id
product_id_comp


milk
1
1A


flour
2
2A


egs
3
3A


sugar
4
4A


salt
5
5A


cakeA
6
1A
1B

cakeA
6
6A
1A

cakeA
6
6A
5A

cakeA
6
6B
2A

cakeB
7
7A
2A

cakeB
7
7B
8A

cakeB
7
7C
3A

bakingsoda
8
8A





CREATE OR REPLACE VIEW public.v_univ_bf AS 
 WITH RECURSIVE recipes_flat AS (
         SELECT v_univ_st.PRODUCT_ID,

            v_univ_st.NAME_PRODUCT,
            v_univ_st.INGREDIENT_ID,
            v_univ_st.PRODUCT_ID_COMP,
      
            (( SELECT count(1) AS count
                   FROM v_univ_st sr
                  WHERE sr.INGREDIENT_ID::text = v_univ_st.PRODUCT_ID_COMP::text)) = 0 AS last_comp_blend,
            1 AS level_blend
           FROM v_univ_st
        UNION ALL
         SELECT 
            e.PRODUCT_ID,

            s.NAME_PRODUCT,
            s.INGREDIENT_ID,
            e.PRODUCT_ID_COMP,
            
            
            (( SELECT count(1) AS count
                   FROM v_univ_st sr
                  WHERE sr.INGREDIENT_ID::text = e.PRODUCT_ID_COMP::text)) = 0 AS last_comp_blend,
            s.level_blend + 1
           FROM v_univ_st e
             JOIN recipes_flat s ON s.PRODUCT_ID_COMP::text = e.INGREDIENT_ID::text
        )
 SELECT recipes_flat.PRODUCT_ID AS id_product,
    recipes_flat.NAME_PRODUCT AS name_product,
    recipes_flat.INGREDIENT_ID AS ingredient_id,
    recipes_flat.PRODUCT_ID_COMP AS product_id_comp,
    recipes_flat.level_blend,
    recipes_flat.last_comp_blend,
        CASE
            WHEN recipes_flat.last_comp_blend AND recipes_flat.level_blend = 1 THEN 'CF'::text
            WHEN recipes_flat.last_comp_blend AND recipes_flat.level_blend > 0 THEN 'F'::text
            WHEN NOT recipes_flat.last_comp_blend AND recipes_flat.level_blend = 1 THEN 'C'::text
            ELSE ''::text
        END AS compact_flat_view
   FROM recipes_flat
ORDER BY id_product, ingredient_id, product_id_comp;

   
   
   select * from v_univ_bf;


id_product
name_product
ingredient_id
product_id_comp
level_blend
last_comp_blend
compact_flat_view


1
milk
1A

1
t
CF

1
cakeA
6A

2
t
F

2
flour
2A

1
t
CF

2
cakeA
6B

2
t
F

2
cakeB
7A

2
t
F

3
egs
3A

1
t
CF

3
cakeB
7C

2
t
F

4
sugar
4A

1
t
CF

5
salt
5A

1
t
CF

5
cakeA
6A

2
t
F

6
cakeA
1A
1B
1
t
CF

6
cakeA
6A
1A
1
f
C

6
cakeA
6A
1B
2
t
F

6
cakeA
6A
5A
1
f
C

6
cakeA
6B
2A
1
f
C

7
cakeB
7A
2A
1
f
C

7
cakeB
7B
8A
1
f
C

7
cakeB
7C
3A
1
f
C

8
cakeB
7B

2
t
F

8
bakingsoda
8A

1
t
CF
name_product	product_id	ingredient_id	product_id_comp
milk	1	1A
flour	2	2A
egs	3	3A
sugar	4	4A
salt	5	5A
cakeA	6	1A	1B
cakeA	6	6A	1A
cakeA	6	6A	5A
cakeA	6	6B	2A
cakeB	7	7A	2A
cakeB	7	7B	8A
cakeB	7	7C	3A
bakingsoda	8	8A