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 sample_data (
  concurrent_users int
, concurrent_timestamp timestamp
, concurrent_date date
);

INSERT INTO sample_data VALUES
(3, '2020-06-24 07:20:00', '2020-06-24'),
(7, '2020-06-24 08:20:00', '2020-06-24'),
(8, '2020-07-05 06:20:00', '2020-07-05'),
(2, '2020-07-05 03:20:00', '2020-07-05');
4 rows affected

SELECT DISTINCT ON (concurrent_date) *
FROM   sample_data
ORDER  BY concurrent_date, concurrent_users DESC;


concurrent_users
concurrent_timestamp
concurrent_date


7
2020-06-24 08:20:00
2020-06-24

8
2020-07-05 06:20:00
2020-07-05


-- we don't need the redundant column concurrent_date
SELECT DISTINCT ON (concurrent_timestamp::date) *
FROM   sample_data
ORDER  BY concurrent_timestamp::date, concurrent_users DESC;


concurrent_users
concurrent_timestamp
concurrent_date


7
2020-06-24 08:20:00
2020-06-24

8
2020-07-05 06:20:00
2020-07-05

concurrent_users	concurrent_timestamp	concurrent_date
7	2020-06-24 08:20:00	2020-06-24
8	2020-07-05 06:20:00	2020-07-05