G.2 Estimating Index Size Requirements

This section provides the formulas for estimating index size requirements.

Open SQL 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 G.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 "G.3 Sizes of Data Types" for details). The key data length must be a multiple of 8 bytes. For example, if the calculation gives 28 bytes, adjust the length to 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 G.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 Round the number of entries per page calculated at (3) to the nearest integer.
(4) Number of pages required for storing indexes	Total number of records / (3) number of entries per page Round the number of pages required for storing indexes calculated at (4) 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 G.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.