I.2 Estimating Index Size Requirements

This section provides the formulas for estimating index size requirements.

Symfoware Server provides five index types: B-tree, Hash, GiST, GIN, and SP-GiST (the same as PostgreSQL). If you do not specify the index type in the CREATE INDEX statement, a B-tree index is generated.

The following describes how to estimate a B-tree index.

A B-tree index is saved as a fixed-size page of 8 KB. The page types are meta, root, leaf, internal, deleted, and empty. Since leaf pages usually account for the highest proportion of space required, you need to calculate the requirements for these only.

Table I.3 Estimation formula when the key data length is 512 bytes or less
Item	Estimation formula (bytes)
(1) Entry length	8 (1) + key data length (2) 1: Entry head 2: The key data length depends on its data type (refer to "I.3 Sizes of Data Types" for details). Because each entry is placed in boundaries of 8 bytes, you need to make an adjustment so that the length of the key data is a multiple of 8. For example, if the calculated length is 28 bytes, add 4 to make the length 32 bytes. f the key data length exceeds 512 bytes, key data may be automatically compressed. In this case, use the estimation formula given in "Table I.4 Estimation formula when the key data length exceeds 512 bytes" to estimate the key data length.
(2) Page size requirement	8152 (1) 1: Page length (8192) - page header (24) - special data (16) = 8152
(3)Number of entries per page	(2) Page size requirement / ((1) entry length + 4 (1)) 1: Pointer length Result of (3) number of entries per page will be rounded down to the nearest integer.
(4) Number of pages required for storing indexes	Total number of records / (3) number of entries per page Result of (4) number of pages required for storing indexes will be rounded up to the nearest integer.
(5) Space requirement	(4) Number of pages required for storing indexes x 8192 (1) / usage rate (2) 1: Page length 2: Specify 0.7 or lower.

Table I.4 Estimation formula when the key data length exceeds 512 bytes
Item	Estimation formula (bytes)
(5) Space requirement	Total number of records x key data length x compression ratio (1) / usage rate (2) 1: The compression ratio depends on the data value, so specify 1. 2: Specify 0.7 or lower as the usage rate.