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

Re: slow self-join query

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Robert Poor <rdpoor(at)gmail(dot)com>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: slow self-join query
Date: 2012-03-18 14:51:21
Message-ID: 4F65F669.2090609@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-performance

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

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-03-18 15:30:05
Subject: Re: slow self-join query
Previous:From: Robert PoorDate: 2012-03-18 14:37:24
Subject: Re: slow self-join query

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