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 mytable(
   client_id VARCHAR(1) NOT NULL 
  ,event     VARCHAR(11) NOT NULL
  ,time      time NOT NULL
);
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:00:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','view','12:05:00');
INSERT INTO mytable(client_id,event,time) VALUES ('A','purchase','14:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','12:04:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','12:07:00');
INSERT INTO mytable(client_id,event,time) VALUES ('B','view','13:20:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','12:00:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','12:07:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','add_to_cart','14:02:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','14:19:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','purchase','14:32:00');
INSERT INTO mytable(client_id,event,time) VALUES ('C','view','15:32:00');

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

client_id	event	cnt	start_time	end_time
A	view	3	12:00:00	12:05:00
A	purchase	1	14:02:00	14:02:00
B	view	3	12:04:00	13:20:00
C	view	2	12:00:00	12:07:00
C	add_to_cart	1	14:02:00	14:02:00
C	view	1	14:19:00	14:19:00
C	purchase	1	14:32:00	14:32:00
C	view	1	15:32:00	15:32:00