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 users(user_id int, name varchar(40))
INSERT INTO USERS
VALUES
(47,'maxim'),
(79,'Kaelthas'),
(52,'Teddy'),
(88,'test'),
(59,'Bagration'),
(51,'Alexey'),
(80,'test1');
7 rows affected

CREATE TABLE followers(_id int, follower_id int, following_id int)
INSERT INTO FOLLOWERS
VALUES
(277,47,79),
(91,79,52),
(2,47,52),
(3,52,47),
(4,47,80),
(5,88,47),
(278,52,59),
(279,52,51),
(281,59,47),
(6,52,79);
10 rows affected

-- 1) кто подписан на 79
WITH cte AS (SELECT F.FOLLOWER_ID, F.FOLLOWING_ID, U.NAME, COUNT(CASE WHEN F.FOLLOWING_ID = '47' THEN '79' 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 ('79','47'))
            SELECT FOLLOWER_ID AS USER_ID, NAME, IS_FOLLOWER
                FROM cte 
                    WHERE FOLLOWING_ID = '79'
                        LIMIT '19' OFFSET '0'


user_id
name
is_follower


47
maxim
f

52
Teddy
t


-- 2) на кого подписан на 79
WITH cte AS (SELECT F.FOLLOWER_ID, F.FOLLOWING_ID, U.NAME, COUNT(CASE WHEN F.FOLLOWER_ID = '47' THEN '79' END) OVER (PARTITION BY F.FOLLOWING_ID) > 0 IS_FOLLOWER
            FROM FOLLOWERS AS F
                LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWING_ID)
                    WHERE F.FOLLOWER_ID IN ('79', '47'))
            SELECT FOLLOWING_ID AS USER_ID, NAME, IS_FOLLOWER
                FROM cte 
                    WHERE FOLLOWER_ID = '79'


user_id
name
is_follower


52
Teddy
t


-- 3) на кого подписан на 47
WITH cte AS (SELECT F.FOLLOWER_ID, F.FOLLOWING_ID, U.NAME, COUNT(CASE WHEN F.FOLLOWER_ID = '47' THEN '47' END) OVER (PARTITION BY F.FOLLOWING_ID) > 0 IS_FOLLOWER
            FROM FOLLOWERS AS F
                LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWING_ID)
                    WHERE F.FOLLOWER_ID IN ('47', '47'))
            SELECT FOLLOWING_ID AS USER_ID, NAME, IS_FOLLOWER
                FROM cte 
                    WHERE FOLLOWER_ID = '47'
                        LIMIT '19' OFFSET '0'


user_id
name
is_follower


52
Teddy
t

79
Kaelthas
t

80
test1
t


-- 4) кто подписан на 47
WITH cte AS (SELECT F.FOLLOWER_ID, F.FOLLOWING_ID, U.NAME, COUNT(CASE WHEN F.FOLLOWER_ID = '47' THEN '47' END) OVER (PARTITION BY F.FOLLOWING_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 ('47','47'))
            SELECT FOLLOWER_ID AS USER_ID, NAME, IS_FOLLOWER
                FROM cte 
                    WHERE FOLLOWING_ID = '47'
                        LIMIT '19' OFFSET '0'


user_id
name
is_follower


52
Teddy
f

88
test
f

59
Bagration
f