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 Naeslund
PGP 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.