subject:"\[GENERAL\] Help Optimizing a Summary Query"

Re: [GENERAL] Help Optimizing a Summary Query

2014-12-11 Thread Robert DiFalco

Thanks Arthur. I don't think there is as big a different between BIGINT and
INTEGER as you think there is. In fact with an extended filesystem you
might not see any difference at all.

As I put in the first emal I am using a GIST index on user.name.

I was really more interested in the LEFT OUTER JOINs vs EXISTS queries and
if there was a better alternative I had not considered.

On Tue, Dec 9, 2014 at 11:44 AM, Arthur Silva arthur...@gmail.com wrote:

 On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco robert.difa...@gmail.com
 wrote:

 I'm sorry, I missed a JOIN on the second variation. It is:

 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 friends f ON f.user_id = 33 AND f.friend_id = u.id
 http://u.id*
 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;


 On Tue, Dec 9, 2014 at 10:15 AM, Robert DiFalco robert.difa...@gmail.com
  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?

 Here are the tables:


 CREATE TABLE users (
   idBIGINT,
   name  VARCHAR,
   imageURL  VARCHAR
   created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   phone_natlBIGINT,   /* 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_idBIGINT,
   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.




 Hello Robert, none of your schemas worked for me, here's a clean version

 CREATE TABLE users (

Re: [GENERAL] Help Optimizing a Summary Query

2014-12-11 Thread Arthur Silva

On Thu, Dec 11, 2014 at 6:52 PM, Robert DiFalco robert.difa...@gmail.com
wrote:

 Thanks Arthur. I don't think there is as big a different between BIGINT
 and INTEGER as you think there is. In fact with an extended filesystem you
 might not see any difference at all.

 As I put in the first emal I am using a GIST index on user.name.

 I was really more interested in the LEFT OUTER JOINs vs EXISTS queries and
 if there was a better alternative I had not considered.

 On Tue, Dec 9, 2014 at 11:44 AM, Arthur Silva arthur...@gmail.com wrote:

 On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco robert.difa...@gmail.com
 wrote:

 I'm sorry, I missed a JOIN on the second variation. It is:

 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 friends f ON f.user_id = 33 AND f.friend_id = u.id
 http://u.id*
 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;


 On Tue, Dec 9, 2014 at 10:15 AM, Robert DiFalco 
 robert.difa...@gmail.com 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?

 Here are the tables:


 CREATE TABLE users (
   idBIGINT,
   name  VARCHAR,
   imageURL  VARCHAR
   created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   phone_natlBIGINT,   /* 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_idBIGINT,
   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.




 Hello

Re: [GENERAL] Help Optimizing a Summary Query

2014-12-11 Thread David G Johnston

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.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Help Optimizing a Summary Query

2014-12-10 Thread Robert DiFalco

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?

Here are the tables:


CREATE TABLE users (
  idBIGINT,
  name  VARCHAR,
  imageURL  VARCHAR
  created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  phone_natlBIGINT,   /* 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_idBIGINT,
  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.

Re: [GENERAL] Help Optimizing a Summary Query

2014-12-09 Thread Robert DiFalco

I'm sorry, I missed a JOIN on the second variation. It is:

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 friends f ON f.user_id = 33 AND f.friend_id = u.id
http://u.id*
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;


On Tue, Dec 9, 2014 at 10:15 AM, Robert DiFalco robert.difa...@gmail.com
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?

 Here are the tables:


 CREATE TABLE users (
   idBIGINT,
   name  VARCHAR,
   imageURL  VARCHAR
   created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   phone_natlBIGINT,   /* 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_idBIGINT,
   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.

Re: [GENERAL] Help Optimizing a Summary Query

2014-12-09 Thread Arthur Silva

On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco robert.difa...@gmail.com
wrote:

 I'm sorry, I missed a JOIN on the second variation. It is:

 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 friends f ON f.user_id = 33 AND f.friend_id = u.id
 http://u.id*
 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;


 On Tue, Dec 9, 2014 at 10:15 AM, Robert DiFalco robert.difa...@gmail.com
 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?

 Here are the tables:


 CREATE TABLE users (
   idBIGINT,
   name  VARCHAR,
   imageURL  VARCHAR
   created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   phone_natlBIGINT,   /* 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_idBIGINT,
   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.




Hello Robert, none of your schemas worked for me, here's a clean version

CREATE TABLE users (
  idBIGINT,
  name  VARCHAR,
  imageURL  VARCHAR,
  created   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  phone_natlBIGINT,
  country_e164  SMALLINT,
  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, friend_id),
  FOREIGN KEY (user_id)REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN

Re: [GENERAL] Help Optimizing a Summary Query

Re: [GENERAL] Help Optimizing a Summary Query

Re: [GENERAL] Help Optimizing a Summary Query

[GENERAL] Help Optimizing a Summary Query

Re: [GENERAL] Help Optimizing a Summary Query

Re: [GENERAL] Help Optimizing a Summary Query

6 matches

Site Navigation

Mail list logo

Footer information