Postgres 16

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 users (
    id varchar(20) PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE


CREATE TABLE ads (
    id varchar(20) PRIMARY KEY,
    owner_id varchar(20) REFERENCES users(id)
);

CREATE TABLE


CREATE TABLE messages (
    id varchar(20) ,
    ad varchar(20) REFERENCES ads(id),
    sender varchar(20) REFERENCES users(id),
    receiver varchar(20) REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP,
    read_at TIMESTAMP
);

CREATE TABLE


INSERT INTO users (id, name) VALUES
('80d1c6070a7d', 'User1'),
('someuuid2', 'User2'),
('someuuid3', 'User3');

INSERT 0 3


INSERT INTO ads (id, owner_id) VALUES
('ad1', '80d1c6070a7d'),
('ad2', 'someuuid2'),
('ad3', 'someuuid3');

INSERT 0 3


INSERT INTO messages (id, ad, sender, receiver, content, created_at, read_at) VALUES
('msg1', 'ad1', '80d1c6070a7d', 'someuuid3', 'Hello', '2023-11-27 10:00:00', '2023-11-27 10:05:00'),
('msg2', 'ad1', 'someuuid2', '80d1c6070a7d', 'Hi', '2023-11-27 10:02:00', NULL),
('msg3', 'ad2', 'someuuid3', '80d1c6070a7d', 'How are you?', '2023-11-27 11:00:00', NULL),
('msg4', 'ad3', '80d1c6070a7d', 'someuuid2', 'Fine, thank you', '2023-11-27 12:00:00', NULL);

INSERT 0 4


SELECT
      u.id AS user_id
    , u.name AS user_name
    , m.ad AS ad_id
    , a.owner_id AS ad_owner_id
    , m.content AS last_message_content
    , m.created_at AS last_message_created_at
    , COUNT(CASE WHEN m.read_at IS NULL THEN 1 END) AS unread_messages_count
FROM users u
JOIN messages m ON u.id = m.sender
    OR u.id = m.receiver
JOIN ads a ON m.ad = a.id
WHERE u.id = '80d1c6070a7d' -- ID to compare with (logged in user's ID)
GROUP BY
      u.id
    , u.name
    , m.ad
    , a.owner_id
    , m.content
    , m.created_at
ORDER BY
      m.created_at DESC


user_id
user_name
ad_id
ad_owner_id
last_message_content
last_message_created_at
unread_messages_count


80d1c6070a7d
User1
ad3
someuuid3
Fine, thank you
2023-11-27 12:00:00
1

80d1c6070a7d
User1
ad2
someuuid2
How are you?
2023-11-27 11:00:00
1

80d1c6070a7d
User1
ad1
80d1c6070a7d
Hi
2023-11-27 10:02:00
1

80d1c6070a7d
User1
ad1
80d1c6070a7d
Hello
2023-11-27 10:00:00
0



SELECT 4

user_id	user_name	ad_id	ad_owner_id	last_message_content	last_message_created_at	unread_messages_count
80d1c6070a7d	User1	ad3	someuuid3	Fine, thank you	2023-11-27 12:00:00	1
80d1c6070a7d	User1	ad2	someuuid2	How are you?	2023-11-27 11:00:00	1
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hi	2023-11-27 10:02:00	1
80d1c6070a7d	User1	ad1	80d1c6070a7d	Hello	2023-11-27 10:00:00	0