Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 43C99475C0F for ; Fri, 15 Mar 2002 15:31:34 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 36482D5F7; Fri, 15 Mar 2002 12:12:14 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 29F585BFD; Fri, 15 Mar 2002 12:12:14 -0800 (PST) Date: Fri, 15 Mar 2002 12:12:14 -0800 (PST) From: Stephan Szabo To: Dmitry Tkach Cc: Gregory Wood , PostgreSQL-General Subject: Re: Why is it not using an index? In-Reply-To: <3C92530D.2050005@openratings.com> Message-ID: <20020315120934.Y54258-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Archive-Number: 200203/843 X-Sequence-Number: 22215 On Fri, 15 Mar 2002, Dmitry Tkach wrote: > explain select count (x) from a ; > > Aggregate=9A (cost=3D100175934.05..100175934.05 rows=3D1 width=3D2) > =9A ->=9A Seq Scan on a=9A (cost=3D100000000.00..100150659.04 rows=3D1011= 0004 width=3D2) > > Am I missing something here again, or will it just not use an index > for aggregation? It won't for something like the above because it needs to test each row to see if it's currently visible to your transaction (which involves reading from the table file anyway) which means you end up reading the entire table plus the index (and paying some costs in random access). If the index had the transaction information the index would be usable but there are issues about doing that as well (you might want to check past messages - especially ones from Tom Lane - on the subject)