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@xxxxxxxx> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
