Thread: index not used for boolean

Hi all,
is it normal that in a select like:

select * from foo where expired;

the index on expired is not used?

If I rewrite that query:

select * from foo where expired = true;

then the index is used!

I'm using postrgres 7.4.2


Regards
Gaetano Mendola

I just ran into this yesterday also. I was trying
select * from foo where expired IS TRUE
and the index was not used, but when I switched it to expired = true it worked... Very frustrating bug.. wasted about 3 hours planning and analyzing the simple query.

On Tue, 2004-05-04 at 00:04, Gaetano Mendola wrote:

Hi all,
is it normal that in a select like:

select * from foo where expired;

the index on expired is not used?

If I rewrite that query:

select * from foo where expired = true;

then the index is used!

I'm using postrgres 7.4.2


Regards
Gaetano Mendola

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org

OK, TODO updated:

    * Allow col IS TRUE/FALSE use an index like col = TRUE/FALSE

---------------------------------------------------------------------------

Greg Johnson wrote:
> I just ran into this yesterday also. I was trying
> select * from foo where expired IS TRUE
> and the index was not used, but when I switched it to expired = true it
> worked... Very frustrating bug.. wasted about 3 hours planning and
> analyzing the simple query.
>
> On Tue, 2004-05-04 at 00:04, Gaetano Mendola wrote:
>
> > Hi all,
> > is it normal that in a select like:
> >
> > select * from foo where expired;
> >
> > the index on expired is not used?
> >
> > If I rewrite that query:
> >
> > select * from foo where expired = true;
> >
> > then the index is used!
> >
> > I'm using postrgres 7.4.2
> >
> >
> > Regards
> > Gaetano Mendola
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> >                http://archives.postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073