Received: from rybvod.kamchatka.su (rybvod.kamchatka.su [212.19.19.253]) by hub.org (8.9.2/8.9.1) with ESMTP id TAA61480 for ; Tue, 2 Feb 1999 19:58:00 -0500 (EST) (envelope-from dvs@rybvod.kamchatka.su) Received: from dvs.rybvod.kamchatka.su (orr-0.intra.rybvod.kam [192.168.101.144]) by rybvod.kamchatka.su (8.8.8/8.8.5) with SMTP id NAA02993; Wed, 3 Feb 1999 13:09:14 +1200 (PS) From: "Vladimir Dobrokhotov" To: "Jackson, DeJuan" , "pgsql-sql" Subject: Re: [SQL] index on int2. Date: Wed, 3 Feb 1999 13:01:17 +1200 Message-ID: <01be4f10$b47cc760$9065a8c0@dvs.rybvod.kamchatka.su> MIME-Version: 1.0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.71.1712.3 X-MimeOLE: Produced By Microsoft MimeOLE V4.71.1712.3 >> 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)