PostgreSQL has real table inheritance

[bordage....@gmail.com]

Hi everyone,

I just wanted to point out a lesser know feature of PostgreSQL: table inheritance.

It’s way cleaner than Django multi-table inheritance, and it should be more efficient too.

I know it’s gonna be hard to add that non-db-agnostic feature to Django, but it could be possible since Django will now have PostgreSQL-specific features.

I’m not sure, but there could be a performance benefit on projects based on django-polymorphic.

Suppose we have these models:

class Parent(PolymorphicModel):

    pass

class Child1(Parent):

    pass

class Child2(Parent):

    pass

Currently, when running Parent.objects.all(), we execute from 1 to 4 queries:

1. SELECT * FROM parent;
2. SELECT * FROM django_contenttype WHERE id in (…);   (executes only the first time, to know which child models the previous request refered to)
3. SELECT * FROM child1 WHERE parent_ptr_id in (…);  (only executes if child1 were found in the first query)
4. SELECT * FROM child2 WHERE parent_ptr_id in (…);  (only executes if child2 were found in the first query)

Currently, when running Child1.objects.all(), we execute 1 query with a join:

1. SELECT * FROM child1 JOIN parent ON id = child1.parent_ptr_id;

Using PostgreSQL table inheritance, we could avoid fetching ContentType objects and avoid doing JOINs.

According to EXPLAIN, avoiding the JOIN would significantly decrease the query cost.

Regards,

Bertrand