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
    FROM FOLLOWERS AS F
        LEFT JOIN USERS AS U ON (U.USER_ID = F.FOLLOWER_ID)
            WHERE F.FOLLOWING_ID = 1
INTERSECT
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