On 24 March 2010 05:17, Nilesh Govindarajan <lists@xxxxxxxxxx> wrote: > On 03/24/2010 12:45 AM, Dean Rasheed wrote: >> >> On 23 March 2010 11:07, Nilesh Govindarajan<lists@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
