I'm building a small niche social network. I'm using postgresql as my main database. This database contains 2 tables (for this questions): "users" and "user_followers".
I've simplified both tables for now.
CREATE SEQUENCE users_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; CREATE TABLE users ( user_id bigint DEFAULT nextval('users_seq') NOT NULL, username character varying(16) NOT NULL, country character varying(2) ); ALTER TABLE users ADD CONSTRAINT users_pk PRIMARY KEY (user_id); CREATE TABLE user_followers ( user_id bigint NOT NULL, follower_id bigint NOT NULL ); ALTER TABLE user_followers ADD CONSTRAINT user_followers_pk PRIMARY KEY (user_id,follower_id); CREATE INDEX user_followers_1 ON user_followers (user_id); CREATE INDEX user_followers_2 ON user_followers (follower_id);
I now want to create a query which fetches some interesting users for a specific user. I want to list them based on the number of followers (more followers are more interesting), but also if they come from the same country. I'm thinking of creating a sort of calculated number based on the number of followers AND the country.
I've come up with the current query:
SELECT users.user_id::int, users.username, (SELECT COUNT(*) FROM user_followers WHERE user_id = users.user_id)::int as followers, ( CASE WHEN users.country = (SELECT country FROM users WHERE user_id = $1) THEN 1 * (SELECT COUNT(*) FROM user_followers WHERE user_id = users.user_id)::int ELSE 0.5 * (SELECT COUNT(*) FROM user_followers WHERE user_id = users.user_id)::int END::float ) as algo_1 FROM users WHERE NOT EXISTS (SELECT 1 FROM user_followers WHERE user_id = users.user_id AND follower_id = $1) AND users.user_id != $1 ORDER BY algo_1 DESC LIMIT 50
$1 = user ID of the users for who I want to fetch interesting users.
As you can see, first I fetch the user ID and the username. After that, with a subselect, I count the total number of followers. The third statement is a CASE-statement. When the countries are the same, I mutiply the number of followers with 1, if not, multiply with 0.5. This means that if I have a user coming from country A and a suggested user with 1000 followers from country A, the number is 1000 (10001). If the suggested user is coming from country B, it's only 500 (10000.5), so less important because coming from another country.
As you can see, in this query, I do 3 subselects. I don't know if these are performant but the are all 3 searching on the user ID. I have an index on user ID in the table user_followers.
I have some basic understanding of postgres but I'm a total beginner with this kind of 'problems'. Are there any people here who can help me a little bit on the right track of making this sort of queries, who are performant when scaling up.