Re: slow self-join query

On 03/18/2012 10:37 AM, Robert Poor wrote:
>
> On Sat, Mar 17, 2012 at 23:09, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com
> <mailto:scott(dot)marlowe(at)gmail(dot)com>> 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=10000 loops=1)
>
> Filter: ((u1.type)::text = 'User::Twitter'::text)
>
> Are you sure you're using an indexable condition?
>
>
> I know that users.type is indexed -- what would keep that from being
> honored? FWIW, I believe that all user.type fields are set to
> User::Twitter, but that will change in the future.
>
>

If all the rows have that value, then using the index would be silly.
Postgres knows from the stats that ANALYZE calculates whether or not
using an index is likely to be more efficient, and avoids doing so in
cases where it isn't.

cheers

andrew