Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id E5461475B72 for ; Fri, 15 Mar 2002 15:23:20 -0500 (EST) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id PAA24256; Fri, 15 Mar 2002 15:23:07 -0500 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma024252; Fri, 15 Mar 02 15:22:44 -0500 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id PAA10125; Fri, 15 Mar 2002 15:26:18 -0500 Message-ID: <3C9258CB.B22E190A@nsd.ca> Date: Fri, 15 Mar 2002 15:25:47 -0500 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-12 i686) X-Accept-Language: en MIME-Version: 1.0 To: Gregory Wood Cc: Dmitry Tkach , PostgreSQL-General Subject: Re: Why is it not using an index? References: <3C924680.4090806@openratings.com> <002801c1cc4f$25dba980$7889ffcc@comstock.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200203/841 X-Sequence-Number: 22213 Really, the PostgreSQL interpreter should be smart enough to figure this out by itself... Gregory Wood wrote: > > > 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 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html