Postgres 14

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 messages (
  message_id serial PRIMARY KEY
, feed_id integer
, posted_at timestamp
, parent_id integer
, share_count integer
);

INSERT INTO messages(message_id, feed_id, posted_at, parent_id, share_count) VALUES
  ( 1, 7, '2015-01-02', 21  ,  123)
, ( 2, 7, '2015-01-03', null, 3411)
, ( 3, 7, '2015-01-04', null, 3411)
, ( 4, 7, '2015-01-05', 21  ,  123)
, ( 5, 7, '2015-01-06', 22  ,  533)

, ( 6, 7, '2015-01-07', 22  ,  533)
, ( 7, 8, '2015-01-07', 23  , 4931)
, ( 8, 9, '2015-01-07', null, null)

, ( 9, 7, '2015-01-08', 23  , 4931)
, (10, 7, '2015-01-09', 21  ,  123)
, (11, 7, '2015-01-10', null,    0)
, (12, 7, '2015-01-11', 21  ,  123)
, (13, 7, '2015-01-12', null,  444);

CREATE INDEX index_feed_id_posted_at on messages (feed_id, posted_at);

-- INCLUDE keyword requires Postgres 11+
CREATE INDEX messages_idx_null ON messages (feed_id, posted_at DESC NULLS LAST, share_count DESC NULLS LAST)
INCLUDE (parent_id, message_id)
WHERE parent_id IS NULL;

CREATE INDEX messages_idx_notnull ON messages (feed_id, posted_at DESC NULLS LAST, share_count DESC NULLS LAST)
INCLUDE (parent_id, message_id)
WHERE parent_id IS NOT NULL;

CREATE TABLE



INSERT 0 13



CREATE INDEX



CREATE INDEX



CREATE INDEX


-- original query
WITH filtered_messages AS (
   SELECT messages.*
   FROM   messages
   WHERE  messages.feed_id IN (7) 
   AND   (messages.posted_at >= '2015-01-01 04:00:00.000000') 
   AND   (messages.posted_at <  '2015-04-28 04:00:00.000000')
   )
SELECT parent_id, message_id, posted_at, share_count
FROM  (
   SELECT DISTINCT ON(COALESCE(parent_id, message_id))
          parent_id, message_id, posted_at, share_count
   FROM   filtered_messages
   ORDER  BY COALESCE(parent_id, message_id), posted_at DESC NULLS LAST
   ) messages
ORDER BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;


parent_id
message_id
posted_at
share_count


23
9
2015-01-08 00:00:00
4931

null
3
2015-01-04 00:00:00
3411

null
2
2015-01-03 00:00:00
3411

22
6
2015-01-07 00:00:00
533

null
13
2015-01-12 00:00:00
444

21
12
2015-01-11 00:00:00
123

null
11
2015-01-10 00:00:00
0



SELECT 7


-- better query
SELECT parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NULL  -- match index condition
UNION ALL
(
SELECT DISTINCT ON(parent_id)
       parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NOT NULL  -- match index condition
ORDER  BY parent_id, posted_at DESC NULLS LAST
)
ORDER  BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;


parent_id
message_id
posted_at
share_count


23
9
2015-01-08 00:00:00
4931

null
3
2015-01-04 00:00:00
3411

null
2
2015-01-03 00:00:00
3411

22
6
2015-01-07 00:00:00
533

null
13
2015-01-12 00:00:00
444

21
12
2015-01-11 00:00:00
123

null
11
2015-01-10 00:00:00
0



SELECT 7

parent_id	message_id	posted_at	share_count
23	9	2015-01-08 00:00:00	4931
null	3	2015-01-04 00:00:00	3411
null	2	2015-01-03 00:00:00	3411
22	6	2015-01-07 00:00:00	533
null	13	2015-01-12 00:00:00	444
21	12	2015-01-11 00:00:00	123
null	11	2015-01-10 00:00:00	0