SQL query not returning all results when too much data

Question

I am a software engineer with a little knowledge about what's going on under the hood of a dbms.

The query presented below finds mutually followed people by two people:

SELECT followed.id
FROM person p1
         JOIN followers p1f ON p1.id = p1f.follower_id
         JOIN person followed ON followed.id = p1f.followed_id
         JOIN followers p2f ON followed.id = p2f.followed_id
         JOIN person p2 ON p2.id = p2f.follower_id
WHERE p1.id = 5 AND p2.id = 8;

If the data size is small, the query returns positive results, but when populated (100 people and 5,400 followers records), the results are incorrect. This query is supposed to return about 20 results, while it returns only 1.

Things I understand:

• relational database systems are not suited for this type of problem

I know this, but I want to analyze it either way.

• there must be limitations when joining, because it would create a huge set of records to be filtered by the WHERE clause.

Right, maybe there is some way to reformulate the query so that there are no join limits. I know that there are Postgres settings that I could adjust, but which settings would you recommend updating?

• There is a graph extension for Postgres that could help

Yes, and I am going to use it, but first I want to understand my problem here.

Answer 1

Things I understand:
relational database systems are not suited for this type of problem

Sorry, but you "understand" incorrectly.
Relational Databases do this kind of thing all the time and they can do it very well.

You don't need all of those joins, not to get just the id's, anyway.
You can get it all from the followers table.

Your Problem Statement is unclear.

Say you want all of the persons followed by exactly two other persons, whose id's you know. Something like this should do the job:

select id  
from followers 
where follower_id in ( 1, 2 )
group by id 
having count( follower_id ) = 2 
order by id ;