Re: Why is it not using an index?

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" <dmitry@openratings.com>
> > To: <pgsql-general@postgresql.org.pgsql-sql@postgresql.org>
> > 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 <kleptog@svana.org>   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.