Re: Index size

On Wed, 2 Mar 2005, Tatsuo Ishii wrote:

> > An other question:
> >
> > Is there any way to prevent duplicates on btree index attribute,
> > PERMITTING them on table?
>
> I can't think of any usefull usage for such an index. Can you explain
> why you need it?

I have a relation like this: (att0 varchar(1000), att1 int4)

i create a b-tree index on att1 ()
i cluster my raltion according to index

now i have a query
select *
form tc2000000000
where att1<=900000000 and att1>=0 ;

As far as i can see from explain analyze an index scan is used:
Index Scan using inst_id_idx on tc2000000000
Index Cond: ((att1 <= 900000000) AND (att1 >= 0))

If for each entry in table, an entry in index is beeing held, then the
index size is populated too fast.

I guess, that postgres uses index to find the first entry satisfying the
index conition, after find the last one and then do a sequential scan on
the appropriate fraction of the table (to take advantage of physical
clustering).

In my case, discrete values on att1 are orders of magnitude less than
number of table raws.

Thus, the big index size is useless for me. I want to avoid the overhead
of scanning such a big index, just permitting ONLY the discrete values to
entry in index. In such a way the whole scenario i presented before for
how i guess, that postgres evaluates my query, is still in use.

I think there must be a way to change the way of index_usage to alter it
to what i 'm looking for.

> --
> Tatsuo Ishii
>