Re: Partial indexes instead of partitions

On Mon, Jun 14, 2010 at 08:27:49AM -0400, David Wilson wrote:
> On Mon, Jun 14, 2010 at 5:24 AM, Leonardo F <m_lists(at)yahoo(dot)it> wrote:
> > > For "inserts" I do not see the reason why it would be better to
> > > use index partitioning because AFAIK b-tree would behave exactly
> > > the same in both cases.
> >
> > no, when the index gets very big inserting random values gets
> > very slow.
>
> Do you have any empirical evidence for this being a real problem, or are you
> simply guessing?

Just guessing here as well, but when you're inserting uniformly
distributed "random" values, then it should slow down quite a lot. You
may happen to be lucky in your distributions and keep the upper nodes
of the tree in cache but with more uniform distributions the less this
is going to happen. The larger an index and the more uniform the
distribution the more time is going to be spent pulling blocks off the
disk.

AFAIU the OP is trying to give the cache a chance of doing some useful
work by partitioning by time so it's going to be forced to go to disk
less.

Slightly more usefully for the OP, have you considered a couple of
"levels" to your hierarchy. Maybe bi-hourly (~15 million records?)
within the current day and move them over into a "day" table at night
(or whenever is better). It would be a good time to cluster the data,
if that would help as well.

--
Sam http://samason.me.uk/