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 dialog_message (
   id          int
 , room_id     int
 , member_id   int
 , message     text
 , readtime    timestamp
);

INSERT INTO dialog_message VALUES 
   (81531, 7992,  1 ,'S:TestMsg1', '2018-07-21 17:27:39')
 , (81532, 7992,  1 ,'S:TestMsg2', NULL)                   -- added 3 rows to make test case more revealing
 , (81535, 7992,  3 ,'S:TestMsg3', '2018-07-21 17:28:01')  --
 , (81537, 7992,  3 ,'S:TestMsg4', '2018-07-21 17:28:39')  --
 , (81543, 7992, 16 ,'R:TestOK'  , '2018-07-21 17:27:55')
 , (81545, 7992, 16 ,'R:hi'      , null)
 , (81546, 7992, 16 ,'R:hi2'     , null)
 , (81547, 7992, 16 ,'R:ho1'     , null)
 , (81548, 7992, 16 ,'R:1111'    , '2018-07-22 19:14:14')
 , (81550, 7992, 16 ,'R:33'      , null)
 , (81551, 7992, 16 ,'R:33abc'   , null) 
 ;

-- For all queries:
CREATE INDEX ON dialog_message(room_id, member_id, id DESC);

-- Only for NOT EXISTS query:
CREATE INDEX ON dialog_message(room_id) WHERE readtime IS NULL;
11 rows affected

-- For small tables / not too many stale rows with readtime IS NULL

SELECT d.*
FROM   dialog_message d
WHERE  d.room_id = 7992
AND    d.readtime IS NULL
AND    NOT EXISTS (
   SELECT FROM dialog_message
   WHERE  room_id   = d.room_id
   AND    member_id = d.member_id
   AND    readtime IS NOT NULL
   AND    id > d.id
   );


id
room_id
member_id
message
readtime


81532
7992
1
S:TestMsg2
null

81550
7992
16
R:33
null

81551
7992
16
R:33abc
null


-- For big tables / lots of stale rows with readtime IS NULL:

WITH RECURSIVE cte AS (  -- get latest row for each member_id
   (
   SELECT *
   FROM   dialog_message
   WHERE  room_id = 7992
   ORDER  BY member_id, id DESC
   LIMIT  1
   )
   UNION ALL
   (
   SELECT d.*
   FROM   cte
   JOIN   dialog_message d USING (room_id)
   WHERE  d.member_id > cte.member_id
   ORDER  BY d.member_id, id DESC
   LIMIT  1
   )
   )
, cte2 AS (
   SELECT *
   FROM   cte
   WHERE  readtime IS NULL  -- IF not null, look for more ...

   UNION ALL
   (
   SELECT d.*
   FROM   cte
   JOIN   dialog_message d USING (room_id, member_id)
   WHERE  d.id < cte.id
   AND    cte.readtime IS NULL
   ORDER  BY d.id DESC
   LIMIT  1
   )
   )
SELECT *
FROM   cte2
WHERE  readtime IS NULL;  -- trim the bounding row with readtime IS NOT NULL


id
room_id
member_id
message
readtime


81532
7992
1
S:TestMsg2
null

81551
7992
16
R:33abc
null

81550
7992
16
R:33
null

id	room_id	member_id	message	readtime
81532	7992	1	S:TestMsg2	null
81550	7992	16	R:33	null
81551	7992	16	R:33abc	null