Postgres 10

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 category (
    id integer primary key,
    type varchar(255),
    label varchar (255),
    parent_id integer
);
insert into category (id, type,           label,    parent_id) 
              values (1,  'organisation', 'Google', null),
                     (2,  'product',      'Gmail',  1),
                     (3,  'organisation', 'Apple',  null),
                     (4,  'product',      'iPhone', 3),
                     (5,  'product',      'Mac',    3);

create table event (
    id integer primary key,
    name varchar (255),
    category_id integer
);
insert into event (id, name, category_id) 
           values (1,  'add', 4),
                  (2,  'delete', 5),
                  (3,  'update', 2); 
                  
insert into category (id, type,           label,    parent_id) 
              values (6,  'module',       'Mobile', 5),    /* has 2 parents  */
                     (7,  'organisation', 'AirBNB', null); /* has no parents */

insert into event (id, name, category_id) 
           values (4,  'event1', 6),
                  (5,  'event2', 7);

5 rows affected

3 rows affected

2 rows affected

2 rows affected

id	name	get_categories
1	add	{"product": "iPhone", "organisation": "Apple"}
2	delete	{"product": "Mac", "organisation": "Apple"}
3	update	{"product": "Gmail", "organisation": "Google"}
4	event1	{"module": "Mobile", "product": "Mac", "organisation": "Apple"}
5	event2	{"organisation": "AirBNB"}