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?.
select version();

version

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

SELECT 1

  CREATE TABLE article(
    article_id          SERIAL PRIMARY KEY,
    article_title       VARCHAR (2100),
    article_group_id    INTEGER 
);

    CREATE TABLE article_to_author(
    ata_id      SERIAL PRIMARY KEY,
    article_id  INTEGER,
    author_id   INTEGER
);

    CREATE TABLE author(
    author_id   SERIAL PRIMARY KEY,
    author_name VARCHAR(500)
);

    CREATE TABLE grade(
    grade_id       SERIAL PRIMARY KEY,
    detail         INTEGER,
    s_g            INTEGER,
    total          INTEGER,
    article_id     INTEGER
);

    CREATE TABLE article_group(
    article_group_id SERIAL PRIMARY KEY,
    article_group    VARCHAR(2100)
);

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE TABLE

insert into article_group values (1,'Group1'), (2,'Group2');
insert into article values (1,'A',1), (2,'B',1), (3,'C',2), (4,'D',2) ;
insert into author values (1,'Author1'), (2,'Author2');
insert into article_to_author values (1,1,1),(2,2,1), (3,3,2), (4,4,2);
insert into grade values(1,1,1,60,1),(2,1,1,40,2);

INSERT 0 2

INSERT 0 4

INSERT 0 2

INSERT 0 4

INSERT 0 2

with cte as (SELECT ag.article_group_id,ag.article_group,au.author_id, au.author_name, avg(gr.total) as avg_total
        FROM article_group ag
        LEFT JOIN article ar on ar.article_group_id=ag.article_group_id
        LEFT JOIN article_to_author ata ON ar.article_id = ata.article_id
        LEFT JOIN author au ON ata.author_id = au.author_id 
        LEFT JOIN grade gr ON ar.article_id = gr.article_id
        group by ag.article_group_id,ag.article_group,au.author_id, au.author_name
        )
       SELECT 
       article_group_id, 
       article_group,
       array_agg('[' || author_id || ',' || author_name || ',' || avg_total || ']')
       from cte group by        article_group_id, 
       article_group

article_group_id
article_group
array_agg

1
Group1
{"[1,Author1,50.0000000000000000]"}

2
Group2
{NULL}

SELECT 2