Обсуждение: Index occupancy

Hi everyone,

I have a question concerning the size of an index...
What I acually did was bulid a btree index on an smallint attribute
within a table with 10^8 rows. The table itself is app. 10GB large and
what I would like to have the smallest possible indeces. Unfortunately
the current size is about 2GB per indexed column (8 columns are indexed
in total) which is too large if the planner is supposed to choose a
bitmap scan between all of the indices.

So what I would like to know is the following:
Is there an easy way to tell postgres to occupy the index pages up to
100 %?
I am working in a decision support system so inserts/deletes etc. do
normally not happen at all?


Thanks,

Tschak

"tschak" <jochen.schlosser@gmail.com> writes:
> I have a question concerning the size of an index...
> What I acually did was bulid a btree index on an smallint attribute
> within a table with 10^8 rows. The table itself is app. 10GB large and
> what I would like to have the smallest possible indeces. Unfortunately
> the current size is about 2GB per indexed column (8 columns are indexed
> in total) which is too large if the planner is supposed to choose a
> bitmap scan between all of the indices.

> So what I would like to know is the following:
> Is there an easy way to tell postgres to occupy the index pages up to
> 100 %?

No, but even if there were it wouldn't make much of a difference.  The
minimum possible size of a PG index is about 16 bytes per entry, which
would still put you at 1.6Gb for that many rows.

            regards, tom lane