Postgres 13

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 table_subject_topics_exams
(
    ids_of_subject_section integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    name_of_subject character varying(50) COLLATE pg_catalog."default" NOT NULL,
    section character varying(50) COLLATE pg_catalog."default",
    topic character varying(50) COLLATE pg_catalog."default" NOT NULL,
    subtopic character varying(50) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT table_subject_topics_exams_pkey PRIMARY KEY (ids_of_subject_section)
)

CREATE TABLE


CREATE UNIQUE INDEX unq_table_subject_topics_exams_3 ON table_subject_topics_exams(name_of_subject, section, topic, subtopic)

CREATE INDEX


INSERT INTO table_subject_topics_exams (name_of_subject, section, topic, subtopic)
    VALUES ('Algebra', 'Mathematics', 'Progressions', 'Number Sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
    ON CONFLICT DO NOTHING;
 

INSERT 0 4


SELECT *
FROM table_subject_topics_exams


ids_of_subject_section
name_of_subject
section
topic
subtopic


1
Algebra
Mathematics
Progressions
Number Sequences

2
Algebra
Mathematics
Progressions
Number sequences

21
Algebra
Mathematics
Progressions
Arithmetic Progression

25
Algebra
Mathematics
Progressions
Geometric Progression



SELECT 4

ids_of_subject_section	name_of_subject	section	topic	subtopic
1	Algebra	Mathematics	Progressions	Number Sequences
2	Algebra	Mathematics	Progressions	Number sequences
21	Algebra	Mathematics	Progressions	Arithmetic Progression
25	Algebra	Mathematics	Progressions	Geometric Progression