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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
