Thread: Not scanning by index

I have a table:

create table forsamling (       id              SERIAL,       for_id          int4 unique not null,       kund_flag
 int8 not null default 1,       online          smallint default 0,       klar            smallint default 0,
 
);

create index forsamling_idx on forsamling(for_id,online,klar,kund_flag);

It has about 1000 entries in this table...

Why doesn't it go by indexes when i search the smallints and int8s, but it
works with the integer SERIAL (SERIAL creates it's own index)?

What can i do to make it go by index?

/Magnus

explain select * from forsamling where klar = 1;
NOTICE:  QUERY PLAN:

Seq Scan on forsamling  (cost=0.00..23.50 rows=1 width=88)

-----

explain select * from forsamling where kund_flag = 123;
NOTICE:  QUERY PLAN:

Seq Scan on forsamling  (cost=0.00..23.50 rows=1 width=88)


-----

explain select * from forsamling where for_id = 123;
NOTICE:  QUERY PLAN:

Index Scan using forsamling_idx on forsamling  (cost=0.00..2.01 rows=1
width=88)


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-Programmer/Networker [|] Magnus NaeslundPGP Key:
http://www.genline.nu/mag_pgp.txt
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

> explain select * from forsamling where klar = 1;

Try SELECT * FROM forsampling WHERE klar = 1::int2

-Mitch

On Thu, 3 May 2001, Magnus Naeslund(f) wrote:

> I have a table:
> 
> create table forsamling (
>         id              SERIAL,
>         for_id          int4 unique not null,
>         kund_flag       int8 not null default 1,
>         online          smallint default 0,
>         klar            smallint default 0,
> );
> 
> create index forsamling_idx on forsamling(for_id,online,klar,kund_flag);
> 
> It has about 1000 entries in this table...
> 
> Why doesn't it go by indexes when i search the smallints and int8s, but it
> works with the integer SERIAL (SERIAL creates it's own index)?
> 
> What can i do to make it go by index?

Two things I can think of that might help...

First, the multi-column indexes aren't very useful for searching for
things not at the start of the index (ie, klar, etc...).

Second, there's a known problem with the other integer types because the
int constant you're comparing against is assumed as an int4.  You need
to explicitly cast the constant to type of the column.