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.

Thanks!

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.