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

Re: slow self-join query

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Robert Poor <rdpoor(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: slow self-join query
Date: 2012-03-18 15:30:05
Message-ID: CAOR=d=3c2RYFCSR+TCLkuLWZ+kK5433SKqJtv_h2qdZpoER_yQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, Mar 18, 2012 at 8:37 AM, Robert Poor <rdpoor(at)gmail(dot)com> wrote:
> On Sat, Mar 17, 2012 at 23:12, Scott
> Marlowe <scott(dot)marlowe(at)gmail(dot)com> 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 loops=1)
>>
>> I'm guessing the f2.follower_id isn't very selective?
>
>
> Not 100% sure what you mean -- f2.follower_id is very sparse (compared to
> f1.follower_id), but that's the point of this particular query.  But since
> upping work_mem makes it run really fast, I'm not overly concerned about
> this one.  Thanks for your help!

Selectivity is how selective is a single value is likely to be.  So if
f2.follower_id has 5000 entries and there's only 2 values, it's not
likely to be very selective, as most of the table will match one of
two values.  If it's 1M rows and 1M distinct follower_ids then it's
selectivity is 1.0 because one value will get just one row ever.

> One last thought: I could re-cast this as a subquery / query pair, each with
> a single join.  Am I correct in thinking that could make it really easy on
> the planner (especially if the tables were properly indexed)?

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?

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-03-18 15:44:46
Subject: Re: slow self-join query
Previous:From: Andrew DunstanDate: 2012-03-18 14:51:21
Subject: Re: slow self-join query

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