Re: Not scanning by index

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.