|
"Data Growth Pty Ltd" <datagrowth@xxxxxxxxx> wrote in message
news:AANLkTimZaiO+7mTuR=sX0jCQwU7Uk+_XZnudL4qRMUsh@xxxxxxxxxxxxxx...
Have you actually tested this? Why do you expect an
improvement? I am quite interested.
Manual states:
"The benefits will normally be worthwhile only when
a table would otherwise be very large. The exact point at which a table will benefit from partitioning depends on the
application, although a rule of
thumb is that the size of the table should exceed
the physical memory of the database server."
Unfortunately, this last sentence is not
explained. What operations would benefit from partitioning and what operations
would not?
Another problem is that there is no time complexity
information in the manual, and it can hardly be found on the net
either.
But here is a try based on my limited
understanding from the docs (and on which I would appreciate some
feedback):
- INSERT on an unconstrained, unindex, etc (i.e.
plain table): O(1). So the table size "in itself" doesn't play a role. But you
probably have indexes. If they are B-trees you probably would be in the range of
O(log(n)). (See http://en.wikipedia.org/wiki/B-tree. Unfortunately
it doesn't show complexity, but it does say in the heading that "B-tree is
optimized for systems that read and write large blocks of data". Also check http://en.wikipedia.org/wiki/B-tree#Insertion)
Now 200M or 2M records... I wouldn't expect much improvement.
-UPDATES: I read somewhere that indexes use
pointers to the data. I suppose your UPDATE-constraints are indexed, so there is
no need for sequential scans on the implicit SELECT. So partitioning will not
give you better performance. System cache will do it's job here.
A possible problem would be if your indexes are
larger than your available memory. What impact that would have I completely
do not know and I think it would be nice if someone could clear that up a bit.
What impact would it have on SELECT? But in your case with 200M records,
the indexes probably fit well into memory?
Regards,
Davor Josipovic
|
