treebeard — Efficient tree model implementations for Django

treebeard

synopsys:Efficient Tree implementations for Django 1.0+
copyright:2008 by Gustavo Picon
license:Apache License 2.0
version:1.2a
url:http://code.google.com/p/django-treebeard/
documentation:treebeard-docs
examples:treebeard-examples (source)
tests:treebeard-tests
benchmarks:treebeard-benchmarks

django-treebeard is a library that implements efficient tree implementations for the Django Web Framework 1.0+. It includes 3 different tree implementations: Adjacency List, Materialized Path and Nested Sets. Each one has it’s own strength and weaknesses (see Benchmarks) but share the same API, so it’s easy to switch between implementations.

django-treebeard uses Django Model Inheritance with abstract classes to let you define your own models. To use django-treebeard:

  1. Download a release from the treebeard download page or get a development version from the treebeard subversion repository.
  2. Run python setup.py install
  3. Add 'treebeard' to the INSTALLED_APPS section in your django settings file.
  4. Create a new model that inherits from one of django-treebeard‘s abstract tree models: mp_tree.MP_Node (materialized path), ns_tree.NS_Node (nested sets) or al_tree.AL_Node (adjacency list).
  5. Run python manage.py syncdb

Read the models.Node API reference for detailed info.

treebeard.models

Django models.

copyright:2008 by Gustavo Picon
license:Apache License 2.0
class treebeard.models.Node(*args, **kwargs)

Bases: django.db.models.base.Model

Node class.

This is the base class that defines the API of all tree models in this library:

  • mp_tree.MP_Node (materialized path)
  • ns_tree.NS_Node (nested sets)
  • al_tree.AL_Node (adjacency list)
classmethod add_root(**kwargs)

Adds a root node to the tree. The new root node will be the new rightmost root node. If you want to insert a root node at a specific position, use add_sibling() in an already existing root node instead.

Parameter:**kwargs – object creation data that will be passed to the inherited Node model
Returns:the created node object. It will be save()d by this method.

Example:

MyNode.add_root(numval=1, strval='abcd')
MyNode.add_root(**{'numval':1, 'strval':'abcd'})
add_child(**kwargs)

Adds a child to the node. The new node will be the new rightmost child. If you want to insert a node at a specific position, use the add_sibling() method of an already existing child node instead.

Parameter:**kwargs – Object creation data that will be passed to the inherited Node model
Returns:The created node object. It will be save()d by this method.

Example:

node.add_child(numval=1, strval='abcd')
node.add_child(**{'numval': 1, 'strval': 'abcd'})
add_sibling(pos=None, **kwargs)

Adds a new node as a sibling to the current node object.

Parameters:
  • pos

    The position, relative to the current node object, where the new node will be inserted, can be one of:

    • first-sibling: the new node will be the new leftmost sibling
    • left: the new node will take the node’s place, which will be moved to the right 1 position
    • right: the new node will be inserted at the right of the node
    • last-sibling: the new node will be the new rightmost sibling
    • sorted-sibling: the new node will be at the right position according to the value of node_order_by
  • **kwargs – Object creation data that will be passed to the inherited Node model
Returns:

The created node object. It will be saved by this method.

Raises InvalidPosition:
 

when passing an invalid pos parm

Raises InvalidPosition:
 

when node_order_by is enabled and the pos parm wasn’t sorted-sibling

Raises MissingNodeOrderBy:
 

when passing sorted-sibling as pos and the node_order_by attribute is missing

Examples:

node.add_sibling('sorted-sibling', numval=1, strval='abcd')
node.add_sibling('sorted-sibling', **{'numval': 1, 'strval': 'abcd'})
delete()

Removes a node and all it’s descendants.

Note

Call our queryset’s delete to handle children removal. Subclasses will handle extra maintenance.

classmethod get_tree(parent=None)
Returns:A list of nodes ordered as DFS, including the parent. If no parent is given, the entire tree is returned.
get_depth()
Returns:the depth (level) of the node

Example:

node.get_depth()
get_ancestors()
Returns:A queryset containing the current node object’s ancestors, starting by the root node and descending to the parent. (some subclasses may return a list)

Example:

node.get_ancestors()
get_children()
Returns:A queryset of all the node’s children

Example:

node.get_children()
get_children_count()
Returns:The number of the node’s children

Example:

node.get_children_count()
get_descendants()
Returns:A queryset of all the node’s descendants, doesn’t include the node itself (some subclasses may return a list).

Example:

node.get_descendants()
get_descendant_count()
Returns:the number of descendants of a node.

Example:

node.get_descendant_count()
get_first_child()
Returns:The leftmost node’s child, or None if it has no children.

Example:

node.get_first_child()
get_last_child()
Returns:The rightmost node’s child, or None if it has no children.

Example:

node.get_last_child()
get_first_sibling()
Returns:The leftmost node’s sibling, can return the node itself if it was the leftmost sibling.

Example:

node.get_first_sibling()
get_last_sibling()
Returns:The rightmost node’s sibling, can return the node itself if it was the rightmost sibling.

Example:

node.get_last_sibling()
get_prev_sibling()
Returns:The previous node’s sibling, or None if it was the leftmost sibling.

Example:

node.get_prev_sibling()
get_next_sibling()
Returns:The next node’s sibling, or None if it was the rightmost sibling.

Example:

node.get_next_sibling()
get_parent(update=False)
Returns:the parent node of the current node object. Caches the result in the object itself to help in loops.
Parameter:update – Updates de cached value.

Example:

node.get_parent()
get_root()
Returns:the root node for the current node object.

Example:

node.get_root()
get_siblings()
Returns:A queryset of all the node’s siblings, including the node itself.

Example:

node.get_siblings()
is_child_of(node)
Returns:True if the node is a child of another node given as an argument, else, returns False
Parameter:node – The node that will be checked as a parent

Example:

node.is_child_of(node2)
is_descendant_of(node)
Returns:True if the node if a descendant of another node given as an argument, else, returns False
Parameter:node – The node that will be checked as an ancestor

Example:

node.is_descendant_of(node2)
is_sibling_of(node)
Returns:True if the node if a sibling of another node given as an argument, else, returns False
Parameter:node – The node that will be checked as a sibling

Example:

node.is_sibling_of(node2)
is_root()
Returns:True if the node is a root node (else, returns False)

Example:

node.is_root()
is_leaf()
Returns:True if the node is a leaf node (else, returns False)

Example:

node.is_leaf()
move(target, pos=None)

Moves the current node and all it’s descendants to a new position relative to another node.

Note

The node can be moved under another root node.

Parameters:
  • target – The node that will be used as a relative child/sibling when moving
  • pos

    The position, relative to the target node, where the current node object will be moved to, can be one of:

    • first-child: the node will be the new leftmost child of the target node
    • last-child: the node will be the new rightmost child of the target node
    • sorted-child: the new node will be moved as a child of the target node according to the value of node_order_by
    • first-sibling: the node will be the new leftmost sibling of the target node
    • left: the node will take the target node’s place, which will be moved to the right 1 position
    • right: the node will be moved to the right of the target node
    • last-sibling: the node will be the new rightmost sibling of the target node
    • sorted-sibling: the new node will be moved as a sibling of the target node according to the value of node_order_by

    Note

    If no pos is given the library will use last-sibling, or sorted-sibling if node_order_by is enabled.

Returns:

None

Raises InvalidPosition:
 

when passing an invalid pos parm

Raises InvalidPosition:
 

when node_order_by is enabled and the pos parm wasn’t sorted-sibling or sorted-child

Raises InvalidMoveToDescendant:
 

when trying to move a node to one of it’s own descendants

Raises PathOverflow:
 

when the library can’t make room for the node’s new position

Raises MissingNodeOrderBy:
 

when passing sorted-sibling or sorted-child as pos and the node_order_by attribute is missing

Examples:

node.move(node2, 'sorted-child')

node.move(node2, 'prev-sibling')
save(force_insert=False, force_update=False)

Saves the current instance. Override this in a subclass if you want to control the saving process.

The ‘force_insert’ and ‘force_update’ parameters can be used to insist that the “save” must be an SQL insert or update (or equivalent for non-SQL backends), respectively. Normally, they should not be set.

classmethod get_first_root_node()
Returns:The first root node in the tree or None if it is empty

Example:

MyNodeModel.get_first_root_node()
classmethod get_last_root_node()
Returns:The last root node in the tree or None if it is empty

Example:

MyNodeModel.get_last_root_node()
classmethod get_root_nodes()
Returns:A queryset containing the root nodes in the tree.

Example:

MyNodeModel.get_root_nodes()
classmethod load_bulk(bulk_data, parent=None, keep_ids=False)

Loads a list/dictionary structure to the tree.

Parameters:
  • bulk_data

    The data that will be loaded, the structure is a list of dictionaries with 2 keys:

    • data: will store arguments that will be passed for object creation, and
    • children: a list of dictionaries, each one has it’s own data and children keys (a recursive structure)
  • parent – The node that will receive the structure as children, if not specified the first level of the structure will be loaded as root nodes
  • keep_ids – If enabled, lads the nodes with the same id that are given in the structure. Will error if there are nodes without id info or if the ids are already used.
Returns:

A list of the added node ids.

Note

Any internal data that you may have stored in your nodes’ data (path, depth) will be ignored.

Note

If your node model has node_order_by enabled, it will take precedence over the order in the structure.

Example:

data = [{'data':{'desc':'1'}},
        {'data':{'desc':'2'}, 'children':[
          {'data':{'desc':'21'}},
          {'data':{'desc':'22'}},
          {'data':{'desc':'23'}, 'children':[
            {'data':{'desc':'231'}},
          ]},
          {'data':{'desc':'24'}},
        ]},
        {'data':{'desc':'3'}},
        {'data':{'desc':'4'}, 'children':[
          {'data':{'desc':'41'}},
        ]},
]
# parent = None
MyNodeModel.load_data(data, None)

Will create:

  • 1
  • 2
    • 21
    • 22
    • 23
      • 231
    • 24
  • 3
  • 4
    • 41
classmethod dump_bulk(parent=None, keep_ids=True)

Dumps a tree branch to a python data structure.

Parameters:
  • parent – The node whose descendants will be dumped. The node itself will be included in the dump. If not given, the entire tree will be dumped.
  • keep_ids – Stores the id value (primary key) of every node. Enabled by default.
Returns:

A python data structure, describen with detail in load_bulk()

Example:

tree = MyNodeModel.dump_bulk()

branch = MyNodeModel.dump_bulk(node_obj)
classmethod find_problems()

Checks for problems in the tree structure.

Read the documentation of this method on every tree class for details.

classmethod fix_tree()

Solves some problems that can appear when transactions are not used and a piece of code breaks, leaving the tree in an inconsistent state.

Read the documentation of this method on every tree class for details.

classmethod get_descendants_group_count(parent=None)

Helper for a very common case: get a group of siblings and the number of descendants (not only children) in every sibling.

Parameter:parent – The parent of the siblings to return. If no parent is given, the root nodes will be returned.
Returns:A list (NOT a Queryset) of node objects with an extra attribute: descendants_count.

Example:

# get a list of the root nodes
root_nodes = MyModel.get_descendants_group_count()

for node in root_nodes:
    print '%s by %s (%d replies)' % (node.comment, node.author,
                                     node.descendants_count)

treebeard.mp_tree — Materialized Path tree

treebeard.mp_tree

Materialized Path Tree.

copyright:2008 by Gustavo Picon
license:Apache License 2.0

This is an efficient implementation of Materialized Path trees for Django 1.0+, as described by Vadim Tropashko in SQL Design Patterns. Materialized Path is probably the fastest way of working with trees in SQL without the need of extra work in the database, like Oracle’s CONNECT BY or sprocs and triggers for nested intervals.

In a materialized path approach, every node in the tree will have a path attribute, where the full path from the root to the node will be stored. This has the advantage of needing very simple and fast queries, at the risk of inconsistency because of the denormalization of parent/child foreign keys. This can be prevented with transactions.

django-treebeard uses a particular approach: every step in the path has a fixed width and has no separators. This makes queries predictable and faster at the cost of using more characters to store a step. To address this problem, every step number is encoded.

Also, two extra fields are stored in every node: depth and numchild. This makes the read operations faster, at the cost of a little more maintenance on tree updates/inserts/deletes. Don’t worry, even with these extra steps, materialized path is more efficient than other approaches.

Note

The materialized path approach makes heavy use of LIKE in your database, with clauses like WHERE path LIKE '002003%'. If you think that LIKE is too slow, you’re right, but in this case the path field is indexed in the database, and all LIKE clauses that don’t start with a % character will use the index. This is what makes the materialized path approach so fast.

class treebeard.mp_tree.MP_Node(*args, **kwargs)

Bases: treebeard.models.Node

Abstract model to create your own Materialized Path Trees.

steplen
Attribute that defines the length of each step in the path of a node. The default value of 4 allows a maximum of 1679615 children per node. Increase this value if you plan to store large trees (a steplen of 5 allows more than 60M children per node). Note that increasing this value, while increasing the number of children per node, will decrease the max depth of the tree (by default: 63). To increase the max depth, increase the max_length attribute of the path field in your model.
alphabet

Attribute: the alphabet that will be used in base conversions when encoding the path steps into strings. The default value, 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ is the most optimal possible value that is portable between the supported databases (which means: their default collation will order the path field correctly).

Note

In case you know what you are doing, there is a test that is disabled by default that can tell you the optimal default alphabet in your enviroment. To run the test you must enable the TREEBEARD_TEST_ALPHABET enviroment variable:

$ TREEBEARD_TEST_ALPHABET=1 python manage.py test treebeard.TestTreeAlphabet

On my Ubuntu 8.04.1 system, these are the optimal values for the three supported databases in their default configuration:

Database Optimal Alphabet
MySQL 5.0.51 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
PostgreSQL 8.2.7 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
Sqlite3 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
node_order_by

Attribute: a list of model fields that will be used for node ordering. When enabled, all tree operations will assume this ordering.

Example:

node_order_by = ['field1', 'field2', 'field3']
path

CharField, stores the full materialized path for each node. The default value of it’s max_length, 255, is the max efficient and portable value for a varchar. Increase it to allow deeper trees (max depth by default: 63)

Note

django-treebeard uses Django’s abstract model inheritance, so:

  1. To change the max_length value of the path in your model, you can’t just define it since you’d get a django exception, you have to modify the already defined attribute:

    class MyNodeModel(MP_Node):
        pass
    
    MyNodeModel._meta.get_field('path').max_length = 1024
    
  2. You can’t rely on Django’s auto_now properties in date fields for sorting, you’ll have to manually set the value before creating a node:

    class TestNodeSortedAutoNow(MP_Node):
        desc = models.CharField(max_length=255)
        created = models.DateTimeField(auto_now_add=True)
        node_order_by = ['created']
    
    TestNodeSortedAutoNow.add_root(desc='foo',
                                   created=datetime.datetime.now())
    

Note

For performance, and if your database allows it, you can safely define the path column as ASCII (not utf-8/unicode/iso8859-1/etc) to keep the index smaller (and faster). Also note that some databases (mysql) have a small index size limit. InnoDB for instance has a limit of 765 bytes per index, so that would be the limit if your path is ASCII encoded. If your path column in InnoDB is using unicode, the index limit will be 255 characters since in MySQL’s indexes, unicode means 3 bytes.

Note

treebeard uses numconv for path encoding: http://code.google.com/p/numconv/

depth
PositiveIntegerField, depth of a node in the tree. A root node has a depth of 1.
numchild
PositiveIntegerField, the number of children of the node.

Warning

Do not change the values of path, depth or numchild directly: use one of the included methods instead. Consider these values read-only.

Warning

Do not change the values of the steplen, alphabet or node_order_by after saving your first model. Doing so will corrupt the tree. If you must do it:

  1. Backup the tree with dump_bulk()
  2. Empty your model’s table
  3. Change depth, alphabet and/or node_order_by in your model
  4. Restore your backup using load_bulk() with keep_ids=True to keep the same primary keys you had.

Warning

Be very careful if you add a Meta class in your mp_tree.MP_Node subclass. You must add an ordering attribute with a single element on it:

class Meta:
    ordering = ['path']

If you don’t, the tree won’t work, since mp_tree.MP_Node completely depends on this attribute.

Example:

class SortedNode(MP_Node):
   node_order_by = ['numval', 'strval']

   numval = models.IntegerField()
   strval = models.CharField(max_length=255)

Read the API reference of treebeard.Node for info on methods available in this class, or read the following section for methods with particular arguments or exceptions.

classmethod add_root(**kwargs)

Adds a root node to the tree.

See: treebeard.Node.add_root()

Raises PathOverflow:
 when no more root objects can be added
add_child(**kwargs)

Adds a child to the node.

See: treebeard.Node.add_child()

Raises PathOverflow:
 when no more child nodes can be added
add_sibling(pos=None, **kwargs)

Adds a new node as a sibling to the current node object.

See: treebeard.Node.add_sibling()

Raises PathOverflow:
 when the library can’t make room for the node’s new position
move(target, pos=None)

Moves the current node and all it’s descendants to a new position relative to another node.

See: treebeard.Node.move()

Raises PathOverflow:
 when the library can’t make room for the node’s new position
classmethod get_tree(parent=None)
Returns:A queryset of nodes ordered as DFS, including the parent. If no parent is given, the entire tree is returned.

See: treebeard.Node.get_tree()

Note

This metod returns a queryset.

classmethod find_problems()

Checks for problems in the tree structure, problems can occur when:

  1. your code breaks and you get incomplete transactions (always use transactions!)
  2. changing the steplen value in a model (you must dump_bulk() first, change steplen and then load_bulk()
Returns:

A tuple of five lists:

  1. a list of ids of nodes with characters not found in the alphabet
  2. a list of ids of nodes when a wrong path length according to steplen
  3. a list of ids of orphaned nodes
  4. a list of ids of nodes with the wrong depth value for their path
  5. a list of ids nodes that report a wrong number of children

Note

A node won’t appear in more than one list, even when it exhibits more than one problem. This method stops checking a node when it finds a problem and continues to the next node.

Note

Problems 1, 2 and 3 can’t be solved automatically.

Example:

MyNodeModel.find_problems()
classmethod fix_tree(destructive=False)

Solves some problems that can appear when transactions are not used and a piece of code breaks, leaving the tree in an inconsistent state.

The problems this method solves are:

  1. Nodes with an incorrect depth or numchild values due to incorrect code and lack of database transactions.
  2. “Holes” in the tree. This is normal if you move/delete nodes a lot. Holes in a tree don’t affect performance,
  3. Incorrect ordering of nodes when node_order_by is enabled. Ordering is enforced on node insertion, so if an attribute in node_order_by is modified after the node is inserted, the tree ordering will be inconsistent.
Parameter:destructive

A boolean value. If True, a more agressive fix_tree method will be attemped. If False (the default), it will use a safe (and fast!) fix approach, but it will only solve the depth and numchild nodes, it won’t fix the tree holes or broken path ordering.

Warning

Currently what the destructive method does is:

  1. Backup the tree with dump_data()
  2. Remove all nodes in the tree.
  3. Restore the tree with load_data()

So, even when the primary keys of your nodes will be preserved, this method isn’t foreign-key friendly. That needs complex in-place tree reordering, not available at the moment (hint: patches are welcome).

Example:

MyNodeModel.fix_tree()

treebeard.al_tree — Adjacency List tree

treebeard.al_tree

Adjacency List Tree.

copyright:2008 by Gustavo Picon
license:Apache License 2.0

This is a simple implementation of the traditional Adjacency List Model for storing trees in relational databases.

In the adjacency list model, every node will have a “parent” key, that will be NULL for root nodes.

Since django-treebeard must return trees ordered in a predictable way, the ordering for models without the node_order_by attribute will have an extra attribute that will store the relative position of a node between it’s siblings: sib_order.

The adjacency list model has the advantage of fast writes at the cost of slow reads. If you read more than you write, use MP_Node instead.

class treebeard.al_tree.AL_Node(*args, **kwargs)

Bases: treebeard.models.Node

Abstract model to create your own Adjacency List Trees.

node_order_by

Attribute: a list of model fields that will be used for node ordering. When enabled, all tree operations will assume this ordering.

Example:

node_order_by = ['field1', 'field2', 'field3']
parent

ForeignKey to itself. This attribute MUST be defined in the subclass (sadly, this isn’t inherited correctly from the ABC in Django 1.0). Just copy&paste these lines to your model:

parent = models.ForeignKey('self',
                           related_name='children_set',
                           null=True,
                           db_index=True)
sib_order

PositiveIntegerField used to store the relative position of a node between it’s siblings. This attribute is mandatory ONLY if you don’t set a node_order_by field. You can define it copy&pasting this line in your model:

sib_order = models.PositiveIntegerField()

Examples:

class AL_TestNode(AL_Node):
    parent = models.ForeignKey('self',
                               related_name='children_set',
                               null=True,
                               db_index=True)
    sib_order = models.PositiveIntegerField()
    desc = models.CharField(max_length=255)

class AL_TestNodeSorted(AL_Node):
    parent = models.ForeignKey('self',
                               related_name='children_set',
                               null=True,
                               db_index=True)
    node_order_by = ['val1', 'val2', 'desc']
    val1 = models.IntegerField()
    val2 = models.IntegerField()
    desc = models.CharField(max_length=255)

Read the API reference of treebeard.Node for info on methods available in this class, or read the following section for methods with particular arguments or exceptions.

get_depth(update=False)
Returns:the depth (level) of the node Caches the result in the object itself to help in loops.
Parameter:update – Updates de cached value.

See: treebeard.Node.get_depth()

treebeard.ns_tree — Nested Sets tree

treebeard.ns_tree

Nested Sets Tree.

copyright:2008 by Gustavo Picon
license:Apache License 2.0

An implementation of Nested Sets trees for Django 1.0+, as described by Joe Celko in Trees and Hierarchies in SQL for Smarties.

Nested sets have very efficient reads at the cost of high maintenance on write/delete operations.

class treebeard.ns_tree.NS_Node(*args, **kwargs)

Bases: treebeard.models.Node

Abstract model to create your own Nested Sets Trees.

node_order_by

Attribute: a list of model fields that will be used for node ordering. When enabled, all tree operations will assume this ordering.

Example:

node_order_by = ['field1', 'field2', 'field3']
depth
PositiveIntegerField, depth of a node in the tree. A root node has a depth of 1.
lft
PositiveIntegerField
rgt
PositiveIntegerField
tree_id
PositiveIntegerField

Warning

Be very careful if you add a Meta class in your ns_tree.NS_Node subclass. You must add an ordering attribute with two elements on it:

class Meta:
    ordering = ['tree_id', 'lft']

If you don’t, the tree won’t work, since ns_tree.NS_Node completely depends on this attribute.

classmethod get_tree(parent=None)
Returns:A queryset of nodes ordered as DFS, including the parent. If no parent is given, all trees are returned.

See: treebeard.Node.get_tree()

Note

This metod returns a queryset.

treebeard.exceptions — Exceptions

treebeard.exceptions

Exceptions

copyright:2008 by Gustavo Picon
license:Apache License 2.0
exception treebeard.exceptions.InvalidPosition
Raised when passing an invalid pos value
exception treebeard.exceptions.InvalidMoveToDescendant
Raised when attemping to move a node to one of it’s descendants.
exception treebeard.exceptions.PathOverflow
Raised when trying to add or move a node to a position where no more nodes can be added (see path and alphabet for more info)
exception treebeard.exceptions.MissingNodeOrderBy
Raised when an operation needs a missing node_order_by attribute

tbbench — Benchmarks

tbbench - Lies, damn lies, and benchmarks for treebeard

synopsys:Benchmarks for treebeard
copyright:2008 by Gustavo Picon
license:Apache License 2.0

tbbench is a django app that isn’t installed by default. I wrote it to find spots that could be optimized, and it may help you to tweak your database settings.

To run the benchmarks:

  1. Add tbbench to your Python path
  2. Add 'tbbench' to the INSTALLED_APPS section in your django settings file.
  3. Run python manage.py syncdb
  4. In the tbbench dir, run python run.py

Note

If the django-mptt package is also installed, both libraries will be tested with the exact same data and operations.

Currently, the available tests are:

  1. Inserts: adds 1000 nodes to a tree, in different places: root nodes, normal nodes, leaf nodes
  2. Descendants: retrieves the full branch under every node several times.
  3. Move: moves nodes several times. This operation can be expensive because involves reodrering and data maintenance.
  4. Delete: Removes groups of nodes.

For every available library (treebeard and mptt), two models are tested: a vanilla model, and a model with a “tree order by” attribute enabled (node_order_by in treebeard, order_insertion_by in mptt).

Also, every test will be tested with and without database transactions (tx).

The output of the script is a reST table, with the time for every test in milliseconds (so small numbers are better).

By default, these tests use the default tables created by syncdb. Even when the results of treebeard are good, they can be improved a lot with better indexing. The Materialized Path Tree approach used by treebeard is very sensitive to database indexing, so you’ll probably want to EXPLAIN your most common queries involving the path field and add proper indexes.

Note

Tests results in Ubuntu 8.04.1 on a Thinkpad T61 with 4GB of ram.

Warning

These results shouldn’t be taken as “X is faster than Y”, but as “both X and Y are very fast”.

Databases tested:

  • MySQL InnoDB 5.0.51a, default settings
  • MySQL MyISAM 5.0.51a, default settings
  • PostgreSQL 8.2.7, default settings, mounted on RAM
  • PostgreSQL 8.3.3, default settings, mounted on RAM
  • SQLite3, mounted on RAM
Test Model innodb myisam pg82 pg83 sqlite
no tx tx no tx tx no tx tx no tx tx no tx tx
Inserts TB MP 3220 2660 3181 2766 2859 2542 2540 2309 2205 1934
TB AL 1963 1905 1998 1936 1937 1775 1736 1631 1583 1457
TB NS 3386 3438 3359 3420 4061 7242 3536 4401 2794 2554
MPTT 7559 9280 7525 9028 5202 14969 4764 6022 3781 3484
TB MP Sorted 4732 5627 5038 5215 4022 4808 3415 3942 3250 3045
TB AL Sorted 1096 1052 1092 1033 1239 999 1049 896 860 705
TB NS Sorted 6637 6373 6283 6313 7548 10053 6717 10941 5907 5461
MPTT Sorted 8564 10729 7947 10221 6077 7567 5490 6894 4842 4284
Descendants TB MP 6298 N/A 6460 N/A 7643 N/A 7132 N/A 10415 N/A
TB AL 56850 N/A 116550 N/A 54249 N/A 50682 N/A 50521 N/A
TB NS 5595 N/A 5824 N/A 10080 N/A 5840 N/A 5965 N/A
MPTT 5268 N/A 5306 N/A 9394 N/A 8745 N/A 5197 N/A
TB MP Sorted 6698 N/A 6408 N/A 8248 N/A 7265 N/A 10513 N/A
TB AL Sorted 59817 N/A 59718 N/A 56767 N/A 52574 N/A 53458 N/A
TB NS Sorted 5631 N/A 5858 N/A 9980 N/A 9210 N/A 6026 N/A
MPTT Sorted 5186 N/A 5453 N/A 9723 N/A 8912 N/A 5333 N/A
Move TB MP 837 1156 992 1211 745 1040 603 740 497 468
TB AL 8708 8684 9798 8890 7243 7213 6721 6757 7051 6863
TB NS 683 658 660 679 1266 2000 650 907 672 637
MPTT 6449 7793 6356 7003 4993 20743 4445 8977 921 896
TB MP Sorted 6730 7036 6743 7023 6410 19294 3622 12380 2622 2487
TB AL Sorted 3866 3731 3873 3717 3587 3599 3394 3371 3491 3416
TB NS Sorted 2017 2017 1958 2078 4397 7981 3892 8110 1543 1496
MPTT Sorted 6563 10540 6427 9358 5132 20426 4601 9428 957 955
Delete TB MP 714 651 733 686 699 689 595 561 636 557
TB AL 975 1093 2199 991 758 847 714 804 843 921
TB NS 745 745 742 763 555 698 430 506 530 513
MPTT 2928 4473 2914 4814 69385 167777 18186 26270 1617 1635
TB MP Sorted 811 751 808 737 798 1180 648 1101 612 565
TB AL Sorted 1030 1030 1055 987 797 1023 760 969 884 859
TB NS Sorted 756 750 728 758 807 847 576 748 501 490
MPTT Sorted 3729 5108 3833 4776 86545 148596 34059 127125 2024 1787

Indices and tables