Re: [PERFORM] slow self-join query

2012-03-19 Thread Kevin Grittner
Robert Poor wrote: > @kevin: I hear you. (I'm deeply steeped in Ruby on Rails and > foolishly assume that it's easy to read.) With that in mind: > > \d user_associations > id | integer | not null default > nextval('followings_id_seq'::regclass) I assume that

Re: [PERFORM] slow self-join query

2012-03-19 Thread Robert Poor
@merlin, @kevin: Thank you both -- I'll try your suggestions as soon as I get back to the mothership. @kevin: I hear you. (I'm deeply steeped in Ruby on Rails and foolishly assume that it's easy to read.) With that in mind: \d user_associations Table "public.u

Re: [PERFORM] slow self-join query

2012-03-19 Thread Kevin Grittner
Robert Poor 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

Re: [PERFORM] slow self-join query

2012-03-19 Thread Merlin Moncure
On Sun, Mar 18, 2012 at 10:57 PM, Robert Poor wrote: > On Sun, Mar 18, 2012 at 08:30, Scott Marlowe wrote: >> Why are you joining twice to the parent table?  If you're trying to >> recurse without a with clause, then wouldn't you join the last table >> to the one before it? > > I'm FAR from being

Re: [PERFORM] slow self-join query

2012-03-18 Thread Robert Poor
On Sun, Mar 18, 2012 at 08:30, Scott Marlowe wrote: > Why are you joining twice to the parent table?  If you're trying to > recurse without a with clause, then wouldn't you join the last table > to the one before it? I'm FAR from being an SQL expert; there's a significant chance that I'm not thin

Re: [PERFORM] slow self-join query

2012-03-18 Thread Scott Marlowe
also also wik On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor wrote: > On Sat, Mar 17, 2012 at 23:07, Scott Marlowe > wrote: >> >> Yeah try setting [work_mem] to something absurd like 500MB and see if the >> plan changes. > > > Suweet!  Sorting now runs in-memory, and that makes a big difference, e

Re: [PERFORM] slow self-join query

2012-03-18 Thread Scott Marlowe
On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor wrote: > On Sat, Mar 17, 2012 at 23:12, Scott > Marlowe  wrote: > >> >> Also also this looks like it's the most expensive operation: >> >> Seq Scan on followings f2 (cost=0.00..93523.95 rows=5534395 width=8) >> (actual time=0.041..19365.834 rows=5535964

Re: [PERFORM] slow self-join query

2012-03-18 Thread Andrew Dunstan
On 03/18/2012 10:37 AM, Robert Poor wrote: On Sat, Mar 17, 2012 at 23:09, Scott Marlowe > wrote: Also it looks like you're still not using the index on this: Subquery Scan u1 (cost=0.00..313.55 rows=50 width=4) (actual time=0.030..147.136 rows=100

Re: [PERFORM] slow self-join query

2012-03-18 Thread Robert Poor
On Sat, Mar 17, 2012 at 23:07, Scott Marlowe wrote: > Yeah try setting [work_mem] to something absurd like 500MB and see if the > plan changes. > Suweet! Sorting now runs in-memory, and that makes a big difference, even when groveling over 1M records (under 12 seconds rather than 7 hours). Resu

Re: [PERFORM] slow self-join query

2012-03-17 Thread Scott Marlowe
On Sat, Mar 17, 2012 at 2:56 PM, Robert Poor wrote: > Disclaimer: this is a re-post, since I wasn't subscribed the first > time I posted.  Pardon if this is a duplicate.] > > The following query is abysmally slow (e.g. 7 hours+).  The goal is to > find "among the users that follow user #1, who do

[PERFORM] slow self-join query

2012-03-17 Thread Robert Poor
Disclaimer: this is a re-post, since I wasn't subscribed the first time I posted. Pardon if this is a duplicate.] The following query is abysmally slow (e.g. 7 hours+). The goal is to find "among the users that follow user #1, who do they also follow?" and to count the latter. SELECT L2.foll