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 tbl (conv_id int, user_id int, content text, sent_time timestamp);
INSERT INTO tbl VALUES
(1, 1, '1st_msg', '1990-01-01 00:01:01')
, (1, 2, '2nd_msg', '1990-01-01 00:01:02')
, (1, 2, '3nd_msg', '1990-01-01 00:01:03')
, (1, 1, '4nd_msg', '1990-01-01 00:01:04')
, (2, 1, '1st_msg', '1990-01-02 00:02:01')
, (2, 2, '2nd_msg', '1990-01-02 00:02:02')
, (2, 2, '3nd_msg', '1990-01-02 00:02:03')
, (2, 1, '4nd_msg', '1990-01-02 00:02:04')
, (3, 3, '1st_msg', '1990-01-02 00:03:01')
, (3, 2, '2nd_msg', '1990-01-02 00:03:02')
, (3, 3, '3nd_msg', '1990-01-02 00:03:03')
, (3, 2, '4nd_msg', '1990-01-02 00:03:04')
;
12 rows affected
SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id)
*
FROM tbl
ORDER BY date_trunc('day', sent_time), conv_id, user_id, sent_time;
| conv_id | user_id | content | sent_time |
|---|---|---|---|
| 1 | 1 | 1st_msg | 1990-01-01 00:01:01 |
| 1 | 2 | 2nd_msg | 1990-01-01 00:01:02 |
| 2 | 1 | 1st_msg | 1990-01-02 00:02:01 |
| 2 | 2 | 2nd_msg | 1990-01-02 00:02:02 |
| 3 | 2 | 2nd_msg | 1990-01-02 00:03:02 |
| 3 | 3 | 1st_msg | 1990-01-02 00:03:01 |