Re: [GENERAL] Help me with this multi-table query
On 24 March 2010 05:17, Nilesh Govindarajan li...@itech7.com wrote: On 03/24/2010 12:45 AM, Dean Rasheed wrote: On 23 March 2010 11:07, Nilesh Govindarajanli...@itech7.com wrote: Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean Thanks a lot !! It worked. How to do it using subqueries ? Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me with this multi-table query
On 03/24/2010 01:14 PM, Dean Rasheed wrote: On 24 March 2010 05:17, Nilesh Govindarajanli...@itech7.com wrote: On 03/24/2010 12:45 AM, Dean Rasheed wrote: On 23 March 2010 11:07, Nilesh Govindarajanli...@itech7.comwrote: Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid0 order by u.uid; I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean Thanks a lot !! It worked. How to do it using subqueries ? Well the problem with the original joined query is that when it is executed there will be an intermediate step where it has to consider many thousands of rows (one for each combination of a node and comment for each user). You can see the number of rows processed from your original query by adding up the counts (about 17000). This problem would be compounded if you added more table joins and counts to the query. One way to re-write it using sub-queries would be something like select v1.uid, v1.nc, v2.cc from (select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid) as v1, (select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid) as v2 where v1.uid=v2.uid order by u.uid This is the equivalent of defining a couple of views for the counts and then selecting from those views. Another possibility would be something like select u.uid, (select count(n.nid) from node n where n.uid = u.uid) as nc, (select count(c.nid) from comments c where c.uid = u.uid) as cc from users u order by u.uid There are probably other ways too. Which is best probably depends on the size and distribution of your data, and any indexes you have. You might benefit from indexes on the uid columns of node and comments, if you don't already have them. Try timing them in psql with \timing, and use EXPLAIN ANALYSE to see how each is executed. Regards, Dean The second method is the best. It takes 3.311 ms to execute. The first method suggested by you takes 5.7 ms, and the worst is my method which takes 60ms (boo). Thanks a lot :) :) :) -- Nilesh Govindarajan Site Server Administrator www.itech7.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 me with this multi-table query
Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; This gives me the output like this - uid | nc | cc -+---+--- 1 | 14790 | 14790 4 | 2684 | 2684 19 | 1170 | 1170 24 |80 |80 29 | 119 | 119 33 |64 |64 36 | 9 | 0 41 |78 |78 42 | 7 | 0 43 | 2 | 0 44 | 2 | 2 50 | 2 | 0 55 | 0 | 0 58 | 0 | 0 60 | 0 | 0 73 | 0 | 0 75 | 0 | 0 76 | 0 | 0 81 | 0 | 0 82 | 0 | 0 85 | 0 | 0 86 | 0 | 0 88 | 0 | 0 90 | 0 | 0 91 | 0 | 0 92 | 0 | 0 93 | 0 | 0 94 | 0 | 0 95 | 0 | 0 (29 rows) Whereas, the output for the individual count queries - 1. select u.uid, count(n.nid) nc from users u left join node n on ( n.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; 2. select u.uid, count(c.nid) cc from users u left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; are as follows - uid | nc -+- 1 | 174 4 | 61 19 | 65 24 | 20 29 | 17 33 | 16 36 | 9 41 | 26 42 | 7 43 | 2 44 | 2 50 | 2 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) uid | cc -+ 1 | 85 4 | 44 19 | 18 24 | 4 29 | 7 33 | 4 36 | 0 41 | 3 42 | 0 43 | 0 44 | 1 50 | 0 55 | 0 58 | 0 60 | 0 73 | 0 75 | 0 76 | 0 81 | 0 82 | 0 85 | 0 86 | 0 88 | 0 90 | 0 91 | 0 92 | 0 93 | 0 94 | 0 95 | 0 (29 rows) Something is seriously wrong. I want nc and cc in just one query. How to ? -- Nilesh Govindarajan Site Server Administrator www.itech7.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me with this multi-table query
On 23 March 2010 11:07, Nilesh Govindarajan li...@itech7.com wrote: Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help me with this multi-table query
On 03/24/2010 12:45 AM, Dean Rasheed wrote: On 23 March 2010 11:07, Nilesh Govindarajanli...@itech7.com wrote: Hi, I want to find out the userid, nodecount and comment count of the userid. I'm going wrong somewhere. Check my SQL Code- select u.uid, count(n.nid) nc , count(c.cid) cc from users u left join node n on ( n.uid = u.uid ) left join comments c on ( c.uid = u.uid ) group by u.uid having u.uid 0 order by u.uid; I think you want select u.uid, count(distinct n.nid) nc , count(distinct c.cid) cc from ... otherwise you're counting each node/comment multiple times as the rows in the join multiply up (note 85 x 174 = 14790). For big tables, this could start to become inefficient, and you might be better off doing your queries 1 and 2 above as sub-queries and joining them in an outer query. Regards, Dean Thanks a lot !! It worked. How to do it using subqueries ? -- Nilesh Govindarajan Site Server Administrator www.itech7.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
