Обсуждение: Help Optimizing a Summary Query
I have users, friends, and friend_requests. I need a query that essentially returns a summary containing:
* user (name, imageURL, bio, ...)
* Friend status (relative to an active user)
* Is the user a friend of the active user?
* Has the user sent a friend request to the active user?
* Has the user received a friend request from the active user?
* # of mutualFriends
* Exclude the active user from the result set.
So I have mocked this up two ways but both have complicated query plans that will be problematic with large data sets. I'm thinking that my lack of deep SQL knowledge is making me miss the obvious choice.
Here's my two query examples:
CASE
WHEN EXISTS(SELECT 1 FROM friends f WHERE f.user_id = 33 AND f.friend_id = u.id) THEN 'isFriend'
WHEN EXISTS(SELECT 1 FROM friend_requests s WHERE s.to_id = 33 AND s.from_id = u.id) THEN 'hasSentRequest'
WHEN EXISTS(SELECT 1 FROM friend_requests r WHERE r.to_id = u.id AND r.from_id = 33) THEN 'hasReceivedRequest'
ELSE 'none'
END AS "friendStatus",
(SELECT COUNT(1)
FROM friends f1
JOIN friends f2 ON f1.friend_id = f2.friend_id
WHERE f1.user_id = 33 AND f2.user_id = u.id) AS mutualFriends
FROM users u
CASE
WHEN f.friend_id IS NOT NULL THEN 'isFriend'
WHEN s.to_id IS NOT NULL THEN 'hasSentRequest'
WHEN r.to_id IS NOT NULL THEN 'hasReceivedRequest'
ELSE 'none'
END AS 'friendStatus',
(SELECT COUNT(1) AS d
FROM friends f1
JOIN friends f2 ON f1.fiend_id = f2.friend_id
WHERE f1.user_id = 33 AND f2.user_id = u.id)
FROM users u
LEFT OUTER JOIN friend_requests s ON s.to_id = 33 AND s.from_id = u.id
LEFT OUTER JOIN friend_requests r ON r.to_id = u.id AND r.from_id = 33
33 is just the id of the active user I am using for testing. The WHERE clause could be anything. I'm just using "u.name" here but I'm more concerned about the construction of the result set than the WHERE clause. These have more or less similar query plans, nothing that would change things factorially. Is this the best I can do or am I missing the obvious?
Here are the tables:
CREATE TABLE users (
id BIGINT,
name VARCHAR,
imageURL VARCHAR
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
phone_natl BIGINT, /* National Phone Number */
country_e164 SMALLINT, /* E164 country code */
email VARCHAR(255),
PRIMARY KEY (id),
UNIQUE (email),
UNIQUE (phone_natl, country_e164)
);
CREATE TABLE friends (
user_id BIGINT,
friend_id BIGINT,
PRIMARY KEY (user_id, user_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_friends_friend ON friends(friend_id);
CREATE TABLE friend_requests (
from_id BIGINT,
to_id BIGINT,
created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (from_id, user_id),
FOREIGN KEY (from_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (to_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE INDEX idx_friend_requests_to ON friend_requests(to_id);
Let me know if you guys need anything else.
Robert DiFalco wrote > I have users, friends, and friend_requests. I need a query that > essentially > returns a summary containing: > > * user (name, imageURL, bio, ...) > * Friend status (relative to an active user) > * Is the user a friend of the active user? > * Has the user sent a friend request to the active user? > * Has the user received a friend request from the active user? > * # of mutualFriends > * Exclude the active user from the result set. > > So I have mocked this up two ways but both have complicated query plans > that will be problematic with large data sets. I'm thinking that my lack > of > deep SQL knowledge is making me miss the obvious choice. > > Here's my two query examples: > > SELECT u.id, u.name, u.imageURL, u.bio, > CASE > WHEN EXISTS(SELECT 1 FROM friends f WHERE f.user_id = 33 AND > f.friend_id = u.id) THEN 'isFriend' > WHEN EXISTS(SELECT 1 FROM friend_requests s WHERE s.to_id = 33 AND > s.from_id = u.id) THEN 'hasSentRequest' > WHEN EXISTS(SELECT 1 FROM friend_requests r WHERE r.to_id = u.id AND > r.from_id = 33) THEN 'hasReceivedRequest' > ELSE 'none' > END AS "friendStatus", > (SELECT COUNT(1) > FROM friends f1 > JOIN friends f2 ON f1.friend_id = f2.friend_id > WHERE f1.user_id = 33 AND f2.user_id = u.id) AS mutualFriends > FROM users u > WHERE u.id != 33 AND u.name LIKE 'John%' ORDER BY u.name; > > SELECT u.id, u.name, u.imageURL, u.bio, > CASE > WHEN f.friend_id IS NOT NULL THEN 'isFriend' > WHEN s.to_id IS NOT NULL THEN 'hasSentRequest' > WHEN r.to_id IS NOT NULL THEN 'hasReceivedRequest' > ELSE 'none' > END AS 'friendStatus', > (SELECT COUNT(1) AS d > FROM friends f1 > JOIN friends f2 ON f1.fiend_id = f2.friend_id > WHERE f1.user_id = 33 AND f2.user_id = u.id) > FROM users u > LEFT OUTER JOIN friend_requests s ON s.to_id = 33 AND s.from_id = u.id > LEFT OUTER JOIN friend_requests r ON r.to_id = u.id AND r.from_id = 33 > WHERE u.id != 33 AND u.name LIKE 'John%' ORDER BY u.name; > > 33 is just the id of the active user I am using for testing. The WHERE > clause could be anything. I'm just using "u.name" here but I'm more > concerned about the construction of the result set than the WHERE clause. > These have more or less similar query plans, nothing that would change > things factorially. Is this the best I can do or am I missing the obvious? I dislike the multiple LEFT JOIN version though I did not try to prove that it possible to give incorrect results. The goal is to avoid looping - so you want to create temporary results that will contain all of the data you plan to need and then join them together. CTE/WITH is the feature that can do this most easily. I have no idea how this will perform relative to the "CASE WHEN EXISTS" version but it seems like it should be faster. Again, I don't believe your original LEFT JOIN query is equivalent to either of these but I cannot be certain without more effort than I am able to put forth. Hybrid SQL Code (note in particular that you cannot have literals in the WITH field alias area...) WITH user_ref (ref_u_id) AS ( VALUES (33) ) , users_vis_a_vis_ref (u_id, ref_id) AS ( ... WHERE u_id != ref_u_id) , user_friend (u_id, ref_u_id, 'Friend' AS status_uf) AS ( ... ) , user_sent_request (u_id, ref_u_id, 'Sent' AS status_usr) AS ( ... ) , user_recv_request (u_id, ref_u_id, 'Received' AS status_urr) AS ( ... ) , user_mutuals (u_id, ref_u_id, ## AS mutual_count) AS ( ... ) SELECT u_id, ref_u_id , COALESCE(status_uf, status_usr, status_urr, 'None') AS FriendStatus , COALESCE(mutual_count, 0) AS MutualFriendCount FROM users_vis_a_vis_ref NATURAL LEFT JOIN user_friend NATURAL LEFT JOIN user_sent_request NATURAL LEFT JOIN user_recv_request NATURAL LEFT JOIN user_mutuals It is safe to use "NATURAL" here since you are fully controlling the source relations since they all come from the CTE/WITH structure. David J. -- View this message in context: http://postgresql.nabble.com/Help-Optimizing-a-Summary-Query-tp5829941p5830198.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.