Re: Hash partitioning.

On Thu, Jun 27, 2013 at 2:12 AM, Nicolas Barbier
<nicolas(dot)barbier(at)gmail(dot)com>wrote:

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

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.

Cheers,

Jeff