From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
To: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] index on int2.
Date: Tue, 2 Feb 1999 12:28:41 -0600
Message-ID: <F10BB1FAF801D111829B0060971D839F637F9A@cpsmail> (raw)
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: djackson@cpsgroup.com, dvs@rybvod.kamchatka.su
Subject: Re: [SQL] index on int2.
In-Reply-To: <F10BB1FAF801D111829B0060971D839F637F9A@cpsmail>
References: <F10BB1FAF801D111829B0060971D839F637F9A@cpsmail>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox