Received: from mail.com-stock.com (mail.com-stock.com [204.255.137.254]) by postgresql.org (Postfix) with ESMTP id 0BB04475BF4 for ; Fri, 15 Mar 2002 15:29:24 -0500 (EST) Received: by mail.com-stock.com (Postfix, from userid 1000) id 49C1A98; Fri, 15 Mar 2002 15:28:39 -0500 (EST) Received: from deimos (deimos.com-stock.com [204.255.137.120]) by mail.com-stock.com (Postfix) with SMTP id 18454D9FD; Fri, 15 Mar 2002 15:28:37 -0500 (EST) Message-ID: <003101c1cc60$d98a70e0$7889ffcc@comstock.com> From: "Gregory Wood" To: "Jean-Luc Lachance" Cc: "PostgreSQL-General" References: <3C924680.4090806@openratings.com> <002801c1cc4f$25dba980$7889ffcc@comstock.com> <3C9258CB.B22E190A@nsd.ca> Subject: Re: Why is it not using an index? Date: Fri, 15 Mar 2002 15:34:46 -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/842 X-Sequence-Number: 22214 It should be. I think the response you'll get from the people on this list is that they're happy to accept a patch... Greg ----- Original Message ----- From: "Jean-Luc Lachance" To: "Gregory Wood" Cc: "Dmitry Tkach" ; "PostgreSQL-General" Sent: Friday, March 15, 2002 3:25 PM Subject: Re: [GENERAL] Why is it not using an index? > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >