Received: from svana.org (svana.org [210.9.66.30]) by postgresql.org (Postfix) with ESMTP id 170BD47779D for ; Sun, 17 Mar 2002 19:09:12 -0500 (EST) Received: from kleptog by svana.org with local (Exim 3.12 #1 (Debian)) id 16mkV7-0006Vf-00; Mon, 18 Mar 2002 10:55:45 +1100 Date: Mon, 18 Mar 2002 10:55:45 +1100 From: Martijn van Oosterhout To: Jean-Luc Lachance Cc: Gregory Wood , Dmitry Tkach , PostgreSQL-General Subject: Re: Why is it not using an index? Message-ID: <20020318105545.A24566@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Jean-Luc Lachance , Gregory Wood , Dmitry Tkach , PostgreSQL-General References: <3C924680.4090806@openratings.com> <002801c1cc4f$25dba980$7889ffcc@comstock.com> <3C9258CB.B22E190A@nsd.ca> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <3C9258CB.B22E190A@nsd.ca>; from jllachan@nsd.ca on Fri, Mar 15, 2002 at 03:25:47PM -0500 X-Archive-Number: 200203/895 X-Sequence-Number: 22266 On Fri, Mar 15, 2002 at 03:25:47PM -0500, Jean-Luc Lachance wrote: > Really, the PostgreSQL interpreter should be smart enough to figure this > out by itself... It is actually, if you put quotes around the number so it is explicitly typed as 'unknown'. The interpreter will then accuratly match the type. Without the quotes the number becomes int4 and so a whole promotion/type hierarchy needs to be built to determine how to relate them. Just put quotes around all your constants and all your problems are solved. > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Martijn van Oosterhout http://svana.org/kleptog/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.