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: (view raw or whole 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`.

In response to


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-2015 The PostgreSQL Global Development Group