> On 26.06.2013 16:41, Yuri Levinsky wrote: > >> Heikki, >> As far as I understand the height of the btree will affect the number of >> I/Os necessary. The height of the tree does not increase linearly with >> the number of records. >
> Now let's compare that with a hash partitioned table, with 1000 partitions
> and a b-tree index on every partition. [..] This is almost equivalent to > just having a b-tree that's one level taller [..] There certainly isn't
> any difference in the number of actual I/O performed.
Imagine that there are a lot of indexes, e.g., 50. Although a lookup (walking one index) is equally fast, an insertion must update al 50 indexes. When each index requires one extra I/O (because each index is one level taller), that is 50 extra I/Os.
Except for pathological conditions like indexing the longest values that can be indexed, a btree insertion rarely needs to split even the lowest internal page, much less all pages up to the root.
...
Additionally: Imagine that the data can be partitioned along some column that makes sense for performance reasons (e.g., some “date” where most accesses are concentrated on rows with more recent dates). The other indexes will probably not have such a performance distribution. Using those other indexes (both for look-ups and updates) in the non-partitioned case, will therefore pull a huge portion of each index into cache (because of the “random distribution” of the non-date data). In the partitioned case, more cache can be spent on the indexes that correspond to the “hot partitions.”
This could well be true for range partitioning on date. It is hard to see how this would work well for hash partitioning on the date, unless you carefully arrange for the number of hash partitions to be about the same as the number of distinct dates in the table.