Thread: RE: [SQL] index on int2.

RE: [SQL] index on int2.

From
"Jackson, DeJuan"
Date:
> Hello,
>
> 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 ...
>
> What I need to do to use index search?
>
> Thanks.
> Vladimir Dobrokhotov

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 '>'.

Hope this helps,
    -DEJ