Postgres 11

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 FOLLOWERS (ID INT, FOLLOWER_ID INT, FOLLOWING_ID INT);
INSERT INTO FOLLOWERS
VALUES
( 1, 1, 2 ),
( 2, 2, 1 ),
( 3, 1, 3 ),
( 4, 3, 2 ),
( 5, 3, 1 ),
( 6, 4, 1 ),
( 7, 5, 3 ),
( 8, 4, 2 ),
( 9, 5, 1 );
9 rows affected

CREATE TABLE USERS (USER_ID INT, NAME VARCHAR(16));
INSERT INTO USERS
VALUES
(1,'USER NAME 1'),
(2,'USER NAME 2'),
(3,'USER NAME 3'),
(4,'USER NAME 4'),
(5,'USER NAME 5');
5 rows affected

SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID
FROM FOLLOWERS AS F
LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID)
WHERE F.FOLLOWING_ID = 1


user_id
name
following_id
follower_id


2
USER NAME 2
1
2

3
USER NAME 3
1
3

4
USER NAME 4
1
4

5
USER NAME 5
1
5


SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID
FROM FOLLOWERS AS F
LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID)
WHERE F.FOLLOWING_ID = 2


user_id
name
following_id
follower_id


1
USER NAME 1
2
1

3
USER NAME 3
2
3

4
USER NAME 4
2
4


SELECT U.USER_ID, U.NAME, F.FOLLOWING_ID, F.FOLLOWER_ID, COUNT(CASE WHEN F.FOLLOWING_ID = 2 THEN 1 END) OVER (PARTITION BY F.FOLLOWER_ID) cnt
FROM FOLLOWERS AS F
LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID)
WHERE F.FOLLOWING_ID IN (1,2)


user_id
name
following_id
follower_id
cnt


1
USER NAME 1
2
1
1

2
USER NAME 2
1
2
0

3
USER NAME 3
1
3
1

3
USER NAME 3
2
3
1

4
USER NAME 4
2
4
1

4
USER NAME 4
1
4
1

5
USER NAME 5
1
5
0


WITH cte AS (SELECT F.FOLLOWER_ID, 
                    F.FOLLOWING_ID, 
                    U.NAME,
                    COUNT(CASE WHEN F.FOLLOWING_ID = 2 THEN 1 END) OVER (PARTITION BY F.FOLLOWER_ID) > 0 is_follower
             FROM FOLLOWERS AS F
             LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID)
             WHERE F.FOLLOWING_ID IN (1,2))
SELECT * 
FROM cte 
WHERE FOLLOWING_ID = 1


follower_id
following_id
name
is_follower


2
1
USER NAME 2
f

3
1
USER NAME 3
t

4
1
USER NAME 4
t

5
1
USER NAME 5
f

user_id	name	following_id	follower_id	cnt
1	USER NAME 1	2	1	1
2	USER NAME 2	1	2	0
3	USER NAME 3	1	3	1
3	USER NAME 3	2	3	1
4	USER NAME 4	2	4	1
4	USER NAME 4	1	4	1
5	USER NAME 5	1	5	0