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.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table result
(
    id                uuid      not null
        constraint result_pkey
            primary key,
    created_at        timestamp not null,
    updated_at        timestamp not null,
    academy_year_id   integer,
    form_id           integer,
    others_assessment jsonb,
    section_id        integer,
    term_id           integer
);

INSERT INTO result (id, created_at, updated_at, academy_year_id, form_id, others_assessment, section_id, term_id) VALUES ('084d1169-114c-45f6-9494-545698689f0f', '2019-10-14 21:35:23.017000', '2019-10-14 21:47:29.608000', 3, 2, '[{"others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "school_header": "Excellent result keep it up", "class_position": "1", "class_master_comment": "Good result keep it up"}, {"others": [{"id": 1, "values": [{"id": 1, "value": 5}, {"id": 2, "value": 4}, {"id": 3, "value": 3}, {"id": 4, "value": 4}]}, {"id": 2, "values": [{"id": 1, "value": 3}, {"id": 2, "value": 3}, {"id": 3, "value": 3}, {"id": 4, "value": 3}, {"id": 5, "value": 3}, {"id": 6, "value": 3}, {"id": 7, "value": 3}]}], "average": 45.5, "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "school_header": "Excellent result keep it up", "class_position": "1", "class_master_comment": "Good result keep it up"}]', 4, 1);

CREATE TABLE



INSERT 0 1


create table assessment
(
    id              uuid      not null
        constraint assessment_pkey
            primary key,
    created_at      timestamp not null,
    updated_at      timestamp not null,
    academy_year_id integer,
    assessment      jsonb,
    form_id         integer,
    section_id      integer,
    subject_id      integer,
    term_id         integer
);


INSERT INTO assessment (id, created_at, updated_at, academy_year_id, assessment, form_id, section_id, subject_id, term_id) VALUES ('632b53ee-1a4d-49bb-93d3-8a1d74bbc916', '2019-09-12 22:13:33.984000', '2019-09-12 22:13:33.984000', 3, '[{"exam": 60.0, "grade": "A", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", ",assessment_1": 9.0, "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 49.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 3, 1);
INSERT INTO assessment (id, created_at, updated_at, academy_year_id, assessment, form_id, section_id, subject_id, term_id) VALUES ('72dd61d7-24ef-4708-b78a-b5e824d8f959', '2019-09-12 22:13:49.758000', '2019-09-12 22:13:49.758000', 3, '[{"exam": 29.5, "grade": "n", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 69.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 2, 1);
INSERT INTO assessment (id, created_at, updated_at, academy_year_id, assessment, form_id, section_id, subject_id, term_id) VALUES ('e41cbacf-c16f-49eb-a95d-4d86e8b894d6', '2019-09-21 01:13:28.406000', '2019-09-21 01:13:28.406000', 3, '[{"exam": 40, "grade": "A", "total": 50, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Aristotle", "result_computed": 0, "subject_teacher_done": 0}, {"exam": 50, "grade": "B", "total": 69, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Elvis", "result_computed": 0, "subject_teacher_done": 0}]', 2, 4, 5, 1);
INSERT INTO assessment (id, created_at, updated_at, academy_year_id, assessment, form_id, section_id, subject_id, term_id) VALUES ('7c03c648-556d-447e-8eca-8cf79d993513', '2019-09-12 22:13:03.421000', '2019-09-12 22:13:03.421000', 3, '[{"exam": 42.5, "grade": "B", "total": 73.5, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 40.5, "grade": "n", "total": 71.5, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]', 2, 4, 4, 1);

CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


select * from assessment;


id
created_at
updated_at
academy_year_id
assessment
form_id
section_id
subject_id
term_id


632b53ee-1a4d-49bb-93d3-8a1d74bbc916
2019-09-12 22:13:33.984
2019-09-12 22:13:33.984
3
[{"exam": 60.0, "grade": "A", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", ",assessment_1": 9.0, "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 49.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]
2
4
3
1

72dd61d7-24ef-4708-b78a-b5e824d8f959
2019-09-12 22:13:49.758
2019-09-12 22:13:49.758
3
[{"exam": 29.5, "grade": "n", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 69.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]
2
4
2
1

e41cbacf-c16f-49eb-a95d-4d86e8b894d6
2019-09-21 01:13:28.406
2019-09-21 01:13:28.406
3
[{"exam": 40, "grade": "A", "total": 50, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Aristotle", "result_computed": 0, "subject_teacher_done": 0}, {"exam": 50, "grade": "B", "total": 69, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Elvis", "result_computed": 0, "subject_teacher_done": 0}]
2
4
5
1

7c03c648-556d-447e-8eca-8cf79d993513
2019-09-12 22:13:03.421
2019-09-12 22:13:03.421
3
[{"exam": 42.5, "grade": "B", "total": 73.5, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 40.5, "grade": "n", "total": 71.5, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]
2
4
4
1



SELECT 4

id	created_at	updated_at	academy_year_id	assessment	form_id	section_id	subject_id	term_id
632b53ee-1a4d-49bb-93d3-8a1d74bbc916	2019-09-12 22:13:33.984	2019-09-12 22:13:33.984	3	[{"exam": 60.0, "grade": "A", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", ",assessment_1": 9.0, "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 49.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]	2	4	3	1
72dd61d7-24ef-4708-b78a-b5e824d8f959	2019-09-12 22:13:49.758	2019-09-12 22:13:49.758	3	[{"exam": 29.5, "grade": "n", "total": 50.0, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 50.0, "grade": "B", "total": 69.0, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]	2	4	2	1
e41cbacf-c16f-49eb-a95d-4d86e8b894d6	2019-09-21 01:13:28.406	2019-09-21 01:13:28.406	3	[{"exam": 40, "grade": "A", "total": 50, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Aristotle", "result_computed": 0, "subject_teacher_done": 0}, {"exam": 50, "grade": "B", "total": 69, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9, "assessment_2": 17, "assessment_3": 5, "student_name": "Anana Elvis", "result_computed": 0, "subject_teacher_done": 0}]	2	4	5	1
7c03c648-556d-447e-8eca-8cf79d993513	2019-09-12 22:13:03.421	2019-09-12 22:13:03.421	3	[{"exam": 42.5, "grade": "B", "total": 73.5, "position": "First", "student_id": "f3df0bc2-7b4c-49b9-86c9-6e6b01628623", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Aristotle", "result_computed": false, "subject_teacher_done": false}, {"exam": 40.5, "grade": "n", "total": 71.5, "position": "Third", "student_id": "3a2e5f48-a945-4594-b56e-367d4af52dc0", "assessment_1": 9.0, "assessment_2": 17.0, "assessment_3": 5.0, "student_name": "Anana Elvis", "result_computed": false, "subject_teacher_done": false}]	2	4	4	1