Thanks a lot.
An other question:
Is there any way to prevent duplicates on btree index attribute,
PERMITTING them on table?
On Tue, 1 Mar 2005, Tatsuo Ishii wrote:
> > I have created a btree index on a 'int4' attribute of a table.
> >
> > After i have inserted 1,000,000 raws in my table, i can see that my index
> > size is 2745 Blocks (8KB each) from pg_class. That means about 21,960 KB
> > size.
> >
> > I try to understand hows is this number generated, because thought that
> > for each new entry in table, there is a new entry in index and that each
> > entry of the index is:
> >
> > 4 Bytes for the int4 attribute
> > and
> > 40 Bytes for oid
> >
> > So 44 * 1,000,000 ~ 42,969 KB
> >
> > Can anybody inform me where I do the mistake?
>
> There's no oid in index tuples. There is an 8-byte long header for
> each index tuple. Since you are inserting 4-byte long user data, you
> index tuples are 12-byte each. Each index tuple needs a "pointer" in a
> block, which is called "item pointer" and that is 4-byte long. Each
> block can hold up to floor((8192-24(page header)-16(special
> data))/(12+4)) = 509 tuples. ceil(1,000,000/509) = 1965 is the blocks
> you need for your index. In addition to this, you need a "meta page"
> and a "root page". So it becomes 1965+1+1 = 1967. Also you need
> "internal pages", whose numer is hard to guess since it depends on the
> actual index tree structure(for example, tree height). From my limited
> experience, for 1,000,000 tuples, you will need at least 7 internal
> pages. Now the number becomes 1967+7 = 1974. Still it's different from
> 2745. If you don't have deleted tuples, the difference probably comes
> from the fact that a btree index can never be 100% occupied. IMO
> 1974/2745 = 0.71 seems not so bad.
> --
> Tatsuo Ishii
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
