Skip site navigation (1) Skip section navigation (2)

Re: slow self-join query

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Robert Poor" <rdpoor(at)gmail(dot)com>, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow self-join query
Date: 2012-03-19 14:27:06
Message-ID: 4F66FBEA020000250004640C@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Robert Poor <rdpoor(at)gmail(dot)com> wrote:
> among users that follow leader 321, who are the most widely
> followed leaders?", or more formally, find all the users that are
> followers of user 321 (via inner join on F1)  Of those users,
> tally up their leaders so we know which leaders are most popular.
 
It sounds like you're looking for something like this:
 
SELECT leader_id, count(*) as count
  FROM user_associations x
  WHERE exists
        (
          SELECT * FROM user_associations y
            WHERE y.follower_id = x.follower_id
              AND y.leader_id = 321
        )
  GROUP BY leader_id
;
 
>  create_table "user_associations", :force => true do |t|
>   t.integer  "follower_id"
>   t.integer  "leader_id"
>  end
 
I don't really know what that means.  In the future, it would make
things easier on those who are trying to help if you either post the
SQL form or go into psql and type `\d tablename`.
 
-Kevin

In response to

Responses

pgsql-performance by date

Next:From: Robert PoorDate: 2012-03-19 15:22:17
Subject: Re: slow self-join query
Previous:From: Merlin MoncureDate: 2012-03-19 13:35:14
Subject: Re: slow self-join query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group