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

CREATE TABLE IF NOT EXISTS public.tbl_student
(
    student_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_name varchar(255) NOT NULL,
    student_class integer,
    student_roll_no integer,
    age integer,
    CONSTRAINT tbl_student_pkey PRIMARY KEY (student_id)
) ;

CREATE TABLE IF NOT EXISTS public.tbl_marks
(
    marks_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    student_id integer NOT NULL,
    subject varchar(255),
    marks integer,
    CONSTRAINT tbl_marks_pkey PRIMARY KEY (marks_id)
) ;

INSERT INTO
    public.tbl_student (student_name, student_class, student_roll_no, age)
VALUES
    ('John Doe',5,1, 12),
    ('Clark Keny', 5,2, 10),
    ('Ross Barkley', 5, 3, 11),
    ('Frank Lampard', 5, 4, 9),
    ('John Terry', 5, 5, 10),
    ('Peter Parker',6,1, 13),
    ('Tony Stark', 6,2,11),
    ('Bruce Wayne', 6, 3,14),
    ('Johnny Depp', 6, 4,11),
    ('Jackie Chan',7,1,15),
    ('John Wick', 7,2,14),
    ('Indiana Jones', 7, 3,11),
    ('Halley Berry', 7, 4,12),
    ('Jane Doe', 7, 5,15),
    ('Martha Kent', 7, 6,13);
    
INSERT INTO
    public.tbl_marks (student_id, subject, marks)
VALUES
    (1, 'Math' , 79),
    (1, 'Science' , 64),
    (2, 'Math' , 69),
    (2, 'Science' , 72),
    (3, 'Math' , 30),
    (3, 'Science' , 50),
    (4, 'Math' , 77),
    (4, 'Science' , 72),
    (5, 'Math' , 84),
    (5, 'Science' , 88),
    (6, 'Math' , 36),
    (6, 'Science' , 54),
    (7, 'Math' , 55),
    (7, 'Science' , 54),
    (8, 'Math' , 66),
    (8, 'Science' , 78),
    (9, 'Math' , 43),
    (9, 'Science' , 20),
    (10, 'Math' ,87 ),
    (10, 'Science' , 92),
    (11, 'Math' , 68),
    (11, 'Science' , 82),
    (12, 'Math' , 78),
    (12, 'Science' , 93),
    (13, 'Math' , 20),
    (13, 'Science' , 22),
    (14, 'Math' , 40),
    (14, 'Science' , 42),
    (15, 'Math' , 52),
    (15, 'Science' , 62);

15 rows affected

30 rows affected

student_id	student_name	student_class	student_roll_no	age	subject	marks	marks_avg
5	John Terry	5	5	10	Math	84	67.8000000000000000
2	Clark Keny	5	2	10	Math	69	67.8000000000000000
4	Frank Lampard	5	4	9	Math	77	67.8000000000000000
1	John Doe	5	1	12	Math	79	67.8000000000000000
7	Tony Stark	6	2	11	Math	55	50.0000000000000000
8	Bruce Wayne	6	3	14	Math	66	50.0000000000000000
12	Indiana Jones	7	3	11	Math	78	57.5000000000000000
10	Jackie Chan	7	1	15	Math	87	57.5000000000000000
11	John Wick	7	2	14	Math	68	57.5000000000000000
4	Frank Lampard	5	4	9	Science	72	69.2000000000000000
2	Clark Keny	5	2	10	Science	72	69.2000000000000000
5	John Terry	5	5	10	Science	88	69.2000000000000000
8	Bruce Wayne	6	3	14	Science	78	51.5000000000000000
6	Peter Parker	6	1	13	Science	54	51.5000000000000000
7	Tony Stark	6	2	11	Science	54	51.5000000000000000
12	Indiana Jones	7	3	11	Science	93	65.5000000000000000
11	John Wick	7	2	14	Science	82	65.5000000000000000
10	Jackie Chan	7	1	15	Science	92	65.5000000000000000