From: Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
To: Jackson, DeJuan <djackson@cpsgroup.com>
To: pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] index on int2.
Date: Wed, 3 Feb 1999 13:01:17 +1200
Message-ID: <01be4f10$b47cc760$9065a8c0@dvs.rybvod.kamchatka.su> (raw)
>> 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)
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: dvs@rybvod.kamchatka.su, djackson@cpsgroup.com
Subject: Re: [SQL] index on int2.
In-Reply-To: <01be4f10$b47cc760$9065a8c0@dvs.rybvod.kamchatka.su>
References: <01be4f10$b47cc760$9065a8c0@dvs.rybvod.kamchatka.su>
* 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