Postgres 12

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 files
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9999999 CACHE 1 ),
    folder_id integer,
    type_id integer,
    title character varying COLLATE pg_catalog."default"
);

CREATE TABLE folders
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 999999 CACHE 1 ),
    title character varying COLLATE pg_catalog."default",
    CONSTRAINT folders_pkey PRIMARY KEY (id)
);

CREATE TABLE types
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9999999 CACHE 1 ),
    title character varying COLLATE pg_catalog."default",
    CONSTRAINT types_pkey PRIMARY KEY (id)
);

insert into files (folder_id, type_id, title) values
(1, 1, 'picture1'),
(1, 1, 'picture2'),
(1, 2, 'picture3'),
(1, 2, 'picture4'),
(1, 2, 'picture5'),
(2, 1, 'picture6'),
(2, 1, 'picture7');

insert into folders (title) values
('folder1'),
('folder2');

insert into types (title) values 
('png'),
('bmp');

7 rows affected

2 rows affected

2 rows affected

folder_id	folder_title	total_count	bmp	png
1	folder1	5	3	2
2	folder2	2	0	2