Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 983EE14D377
	for <pgsql-performance@postgresql.org>;
	Sun, 18 Mar 2012 11:51:50 -0300 (ADT)
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andrew@dunslane.net>) id 1S9HSX-0002ar-AR
	for pgsql-performance@postgresql.org; Sun, 18 Mar 2012 14:51:50 +0000
X-Originating-IP: 217.70.178.137
Received: from mfilter8-d.gandi.net (mfilter8-d.gandi.net [217.70.178.137])
	by relay4-d.mail.gandi.net (Postfix) with ESMTP id 8CF4C1720E7;
	Sun, 18 Mar 2012 15:51:25 +0100 (CET)
X-Virus-Scanned: Debian amavisd-new at mfilter8-d.gandi.net
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by mfilter8-d.gandi.net (mfilter8-d.gandi.net [10.0.15.180])
	(amavisd-new, port 10024)
	with ESMTP id je50DlD5g8oD; Sun, 18 Mar 2012 15:51:24 +0100 (CET)
X-Originating-IP: 98.27.54.176
Received: from [192.168.10.146] (cpe-098-027-054-176.nc.res.rr.com
	[98.27.54.176]) (Authenticated sender: adsend@dunslane.net)
	by relay4-d.mail.gandi.net (Postfix) with ESMTPSA id 60DCF172071;
	Sun, 18 Mar 2012 15:51:23 +0100 (CET)
Message-ID: <4F65F669.2090609@dunslane.net>
Date: Sun, 18 Mar 2012 10:51:21 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.1) Gecko/20120216 Thunderbird/10.0.1
MIME-Version: 1.0
To: Robert Poor <rdpoor@gmail.com>
CC: Scott Marlowe <scott.marlowe@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: slow self-join query
References: 
 <CAGHqdqWxW2F=xX4N7Haxe_qEPSLA82z_A+-P6_tMAGHUk3wGqQ@mail.gmail.com>
	<CAOR=d=00LZM8=YCR_fqVFyKJjmidYMMLHm_v=+a0qXYfkCVkeQ@mail.gmail.com>
	<CAGHqdqUuE8rKRqfORwRMOBBSQj+QLd6trdE5b2BC3OhuOgFoQw@mail.gmail.com>
	<CAOR=d=05GXYEB4n8WJNFzN8__xT2pFVdEXxhodPB9fzsSw+1Pw@mail.gmail.com>
	<CAGHqdqXqLNXQ48cidqP+nS4Zp+22xWrOanMkJX4tY4jri1eYxQ@mail.gmail.com>
	<CAOR=d=3Td3ZchcfYwQVMwxm+jN2BuEmHCZC6n3s4tBxdT=GJVA@mail.gmail.com>
	<CAGHqdqVk1ZCNY-xP0D=fBLLFCFkjNQEoDoYBONXYv-iHrQTdpA@mail.gmail.com>
	<CAOR=d=1OYRF8mHJf6VPGe3rimeT3jim8ps0h76SiVtuPV9dDmQ@mail.gmail.com>
	<CAGHqdqU4C7JhdCdb47OHO6KTKa4YwxSAZffnMyLYsVD0Bt1T5g@mail.gmail.com>
In-Reply-To: 
 <CAGHqdqU4C7JhdCdb47OHO6KTKa4YwxSAZffnMyLYsVD0Bt1T5g@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201203/123
X-Sequence-Number: 46573



On 03/18/2012 10:37 AM, Robert Poor wrote:
>
> On Sat, Mar 17, 2012 at 23:09, Scott Marlowe <scott.marlowe@gmail.com 
> <mailto:scott.marlowe@gmail.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