martinknappe wrote:
When I change the page size from 4kbyte to 16 kbyte my queries execute way faster. But what's all the fuss about?
Ann W. Harrison answers:
Firebird supports only five database page sizes, 1K, 2K, 4K, 8K, and 16K. The two major performance issues are in indexes: depth and time to scan a page.
The depth is the number of levels of indexes. Indexes are upside down trees with the root at the top and the leaves at the bottom. The root is a single page. The leaf level pages contain pairs of key values and the corresponding record number. In a single level index, the root is the leaf. In a multi-level index, the root contains pairs of index key values and the page number of an lower level page that starts with that key value. In a two level index the root points to leaf page. In a three level index (the most common for the page sizes and data volumes we deal with) the root points to an intermediate level page structured just like the root, pointing to leaf pages. Indexes with more than three levels hurt performance. Use gstat to get the index statistics. If you have indexes that are more than three levels, increase the page size.
The second issue is the time to scan an index page. Index keys are compressed and variable length. If you're looking for the key value "abcdef" and the page starts with "abaaab", you must look at each key until you find "abcdef" or a higher value. That's not much work, but it is some and in larger page sizes the cost of scanning becomes significant. V2 addresses that problem with an internal index to the index page. That will make 16K pages more attractive.