Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id A79A92E0045 for ; Thu, 19 Jun 2008 11:13:13 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08210-02 for ; Thu, 19 Jun 2008 11:12:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from trinity.develer.com (ns1.codewiz.org [89.97.188.34]) by developer.postgresql.org (Postfix) with ESMTP id 961EF2E00AF for ; Thu, 19 Jun 2008 11:03:41 -0300 (ADT) Received: (qmail 19313 invoked by uid 48); 19 Jun 2008 14:03:38 -0000 Received: from 82.111.147.97 (SquirrelMail authenticated user piro) by www.develer.com with HTTP; Thu, 19 Jun 2008 16:03:38 +0200 (CEST) Message-ID: <36573.82.111.147.97.1213884218.squirrel@www.develer.com> In-Reply-To: <485A1CA6.EE98.0025.0@wicourts.gov> References: <4859B13F.3070103@develer.com> <485A1CA6.EE98.0025.0@wicourts.gov> Date: Thu, 19 Jun 2008 16:03:38 +0200 (CEST) Subject: Re: An "obvious" index not being used From: "Daniele Varrazzo" To: "Kevin Grittner" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.13-1.fc9 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200806/100 X-Sequence-Number: 30450 >>>> Daniele Varrazzo wrote: > >> select count(*) from foo >> where foo.account_id in ( >> select id from accounts where system = 'abc'); > >> Total runtime: 13412.226 ms > > Out of curiosity, how does it do with the logically equivalent?: > > select count(*) from foo > where exists (select * from accounts > where accounts.id = foo.account_id > and accounts.system = 'abc'); I tried it: it is slower and the query plan still includes the seqscan: Aggregate (cost=44212346.30..44212346.31 rows=1 width=0) (actual time=21510.468..21510.469 rows=1 loops=1) -> Seq Scan on foo (cost=0.00..44205704.40 rows=2656760 width=0) (actual time=0.058..21402.752 rows=92790 loops=1) Filter: (subplan) SubPlan -> Index Scan using accounts_pkey on accounts (cost=0.00..8.27 rows=1 width=288) (actual time=0.002..0.002 rows=0 loops=5313519) Index Cond: (id = $0) Filter: (("system")::text = 'abc'::text) Total runtime: 21510.531 ms Here the estimate is even more gross: 2656760 is exactly the 50% of the records in the table. -- Daniele Varrazzo - Develer S.r.l. http://www.develer.com