Re: [SQL] index on int2.

>> I create index on int2 field, and it never used:
>>
>> create index xxx on ttt using btree(int2_field);
>> vacuum ttt; (2600 tuples)
>> explain select * from ttt where int2_field>1;
>> ... Seq Scan on ttt ...

>I don't think you got an answer for this yet so...
>
>Think of it this way... Is it cheaper to go the disk read in an index
>and relate those to the rows of the table concerned, then go and fetch
>each of the pages required to print out the results requested?
>The answer to that question depends on the percentage of table pages
>that will need to be loaded anyway. So, (wisely IMHO) Postgres say "Hey
>you're about to load in over 3/4 of this table why should I waste the
>seek time to load in the index as well when I mostly won't be using it.
>So I guess I'll just load em' all in, which with a good file system and
>caching is faster than seeking for the pages in index order, and just
>run your little int compare over those rows for ya."
>
>If you want to see if your index is being used try '=' instead of '>'.

I create table with 71160 rows, 12 fields (3 int2, 5 int4, 3 date,1 bool),
create indexes (btree and hash) on int2 field.
=>explain select * from tst4 where nom=4;
Seq Scan on tst4 (cost=3082.28 size=2 width=39)
=>select * from tst4 where nom=4;
... (104 rows)
=>explain select * from tst4 where nom<4;
Seq Scan on tst4 (cost=3082.28 size=23721 width=39)

I cannot see "Index Scan.." on int2 field...
I create same table(tst5), but index field as int4 and index used, select
work faster!
Need I use int4 instead int2? (I use values between 1 and 1800 only.)
"vacuum" not help anywhere.

Thanks.
Vladimir Dobrokhotov

P.S. interesting: file size:
tst4 (tst5): 6012928 (6627328)
hash index: 2564096 (same)
btree index: 1761280 (same)
(PostgreSQL 6.4.0 on freebsd 2.2.5)