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 my_table (
    the_id varchar(5) NOT NULL, 
    the_date timestamp NOT NULL,
    the_city varchar(5) NOT NULL,
    the_product varchar(1) NOT NULL
);

INSERT INTO my_table
VALUES ('VIS01', '2019-05-02 09:00:00','LISBO','A'), 
       ('VIS02', '2019-05-04 12:00:00','EVORA','A'), 
       ('VIS03', '2019-05-05 18:00:00','LISBO','B'), 
       ('VIS04', '2019-05-06 18:30:00','PORTO','B'), 
       ('VIS05', '2019-05-15 12:05:00','PORTO','C'), 
       ('VIS06', '2019-06-30 18:06:00','EVORA','C'),
       ('VIS07', '2019-06-30 18:07:00','PORTO','A'),
       ('VIS08', '2019-06-30 18:08:00','EVORA','B'),
       ('VIS09', '2019-06-30 18:09:00','LISBO','B'),
       ('VIS10', '2019-06-30 18:10:00','LISBO','D'),
       ('VIS11', '2019-06-30 18:11:00','EVORA','D'),
       ('VIS12', '2019-06-30 18:12:00','LISBO','E'),
       ('VIS13', '2019-06-30 18:13:00','EVORA','F'),
       ('VIS14', '2019-06-30 18:14:00','PORTO','G'),
       ('VIS15', '2019-06-30 18:15:00','LISBO','A'),
       ('VIS16', '2019-06-30 18:16:00','LISBO','A'),
       ('VIS17', '2019-06-30 18:17:00','LISBO','F'),
       ('VIS18', '2019-06-30 18:18:00','LISBO','A'),
       ('VIS19', '2019-06-30 18:19:00','LISBO','A'),
       ('VIS20', '2019-06-30 18:20:00','EVORA','D'),
       ('VIS21', '2019-06-30 18:21:00','EVORA','D'),
       ('VIS22', '2019-06-30 18:30:00','EVORA','D'),
       ('VIS23', '2019-06-30 18:31:00','EVORA','B'),
       ('VIS24', '2019-06-30 18:40:00','EVORA','K'),
       ('VIS25', '2019-06-30 18:50:00','EVORA','G'),
       ('VIS26', '2019-06-30 18:00:00','PORTO','C'),
       ('VIS27', '2019-06-30 18:00:00','PORTO','C'),
       ('VIS28', '2019-06-30 18:00:00','PORTO','B'),
       ('VIS29', '2019-06-30 18:00:00','PORTO','M');

CREATE TABLE

INSERT 0 29

the_city	the_product	product_count
EVORA	D	4
EVORA	B	2
LISBO	A	5
LISBO	B	2
PORTO	C	3
PORTO	B	2

SELECT 6