Received: from mail.com-stock.com (mail.com-stock.com [204.255.137.254]) by postgresql.org (Postfix) with ESMTP id C48B4475E29 for ; Fri, 15 Mar 2002 13:22:41 -0500 (EST) Received: by mail.com-stock.com (Postfix, from userid 1000) id A249058; Fri, 15 Mar 2002 13:21:55 -0500 (EST) Received: from deimos (deimos.com-stock.com [204.255.137.120]) by mail.com-stock.com (Postfix) with SMTP id 6D1A2D9FB; Fri, 15 Mar 2002 13:21:53 -0500 (EST) Message-ID: <002801c1cc4f$25dba980$7889ffcc@comstock.com> From: "Gregory Wood" To: "Dmitry Tkach" Cc: "PostgreSQL-General" References: <3C924680.4090806@openratings.com> Subject: Re: Why is it not using an index? Date: Fri, 15 Mar 2002 13:28:04 -0500 Organization: Comstock Net Services MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200203/836 X-Sequence-Number: 22208 > explain select * from a where x=3; PostgreSQL is treating 3 as an int4 (integer) type, whereas x is an int2 (smallint) type. Try casting the constant as a smallint and it should use the index: explain select * from a where x=3::smallint; Greg ----- Original Message ----- From: "Dmitry Tkach" To: Sent: Friday, March 15, 2002 2:07 PM Subject: [GENERAL] Why is it not using an index? > This must be really simple, but I just can't get it :-( > I have a table (a) with a single column (x): > > Table "a" > Attribute | Type | Modifier > -----------+----------+---------- > x | smallint | > Index: a_idx > > > Index "a_idx" > Attribute | Type > -----------+---------- > x | smallint > btree > > The table has 10000000 rows.... > > Now, how come, when I do: > > explain select * from a where x=3; > > it says: > > Seq Scan on bset (cost=100000000.00..100175934.05 rows=303 width=2) > > Why is it not using a_idx??? > > I even tried set enable_seqscan to off - makes no difference :-( > > Any idea what is going on? > > Thanks a lot! > > Dima > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >