pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex on int2.
3+ messages / 2 participants
[nested] [flat]
* index on int2.
@ 1999-01-29 05:40 Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
0 siblings, 0 replies; 3+ messages in thread
From: Vladimir Dobrokhotov @ 1999-01-29 05:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: [SQL] index on int2.
@ 1999-02-02 18:28 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jackson, DeJuan @ 1999-02-02 18:28 UTC (permalink / raw)
To: Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] index on int2.
@ 1999-02-03 01:01 Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
0 siblings, 0 replies; 3+ messages in thread
From: Vladimir Dobrokhotov @ 1999-02-03 01:01 UTC (permalink / raw)
To: Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-sql
>> 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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-02-03 01:01 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-29 05:40 index on int2. Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
1999-02-02 18:28 RE: [SQL] index on int2. Jackson, DeJuan <djackson@cpsgroup.com>
1999-02-03 01:01 Re: [SQL] index on int2. Vladimir Dobrokhotov <dvs@rybvod.kamchatka.su>
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