> 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
