pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Andrew Dunstan <andrew@dunslane.net>
To: Robert Poor <rdpoor@gmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: slow self-join query
Date: Sun, 18 Mar 2012 10:51:21 -0400
Message-ID: <4F65F669.2090609@dunslane.net> (raw)
In-Reply-To: <CAGHqdqU4C7JhdCdb47OHO6KTKa4YwxSAZffnMyLYsVD0Bt1T5g@mail.gmail.com>
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>



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



view thread (11+ messages)  latest in thread

Message-ID: <4F65F669.2090609@dunslane.net>
Permalink:  ../4F65F669.2090609@dunslane.net/
Also on:    postgresql.org/message-id/4F65F669.2090609@dunslane.net

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: andrew@dunslane.net, rdpoor@gmail.com, scott.marlowe@gmail.com
  Subject: Re: slow self-join query
  In-Reply-To: <4F65F669.2090609@dunslane.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox