Re: Hash partitioning.

2013/6/26 Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>:

> 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. In the partitioned case,
each index would require the normal smaller amount of I/Os. Choosing
which partition to use must only be done once: The result “counts” for
all indexes that are to be updated.

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.”

Nicolas

--
A. Because it breaks the logical sequence of discussion.
Q. Why is top posting bad?