Tuning Django ORM (part 2): Many-To-Many Queries

Creating and using many-to-many (m2m) related models with Django is, most of the time, really easy-going. In fact, it might sometimes be that easy, that you only realize how complex the data relations are, when your application comes to a grinding halt because of „simply retrieving lists of names related to some other objects“.

This article will focus on Django 1.8 with a backing PostgreSQL 9.3. It probably applies to older Django versions, as well.

The problem discussed here only shows up when retrieving more data without paging as you would to export data, e.g. in CSV format.

In our case, the first solution to increase performance was to use raw SQL instead of the Django ORM. This gained significant performance at the cost of writing out all database table and column names explicitly.

Our second and current solution is a mix. It uses the Django ORM for all non-m2m fields, and the „select“ function with raw SQL for the m2m fields.

# python
Article.objects.all().extra(select={'publishers':"""
SELECT string_agg(name, ', ' ORDER BY name ASC)
FROM app_publisher, app_article_publishers
WHERE app_article.id=app_article_publishers.article_id
AND app_article_publishers.publisher_id=app_publisher.id
""", 'authors': """
SELECT string_agg(name, ', ' ORDER BY name)
FROM app_author, app_article_authors
WHERE app_article.id=app_article_authors.article_id
AND app_article_authors.author_id=app_author.id
"""}).values('title', 'publishers', 'authors')

# SQL
EXPLAIN ANALYZE SELECT (
SELECT string_agg(name, ', ' ORDER BY name)
FROM app_author, app_article_authors
WHERE app_article.id=app_article_authors.article_id
AND app_article_authors.author_id=app_author.id
) AS "authors", (
SELECT string_agg(name, ', ' ORDER BY name ASC)
FROM app_publisher, app_article_publishers
WHERE app_article.id=app_article_publishers.article_id
AND app_article_publishers.publisher_id=app_publisher.id
) AS "publishers", "app_article"."title" FROM "app_article";
                                                                                                                                                                                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on app_article  (cost=0.00..448663.36 rows=19955 width=21) (actual time=0.276..1307.483 rows=19974 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=14.76..14.77 rows=1 width=16) (actual time=0.047..0.047 rows=1 loops=19974)
           ->  Hash Join  (cost=4.35..14.76 rows=2 width=16) (actual time=0.019..0.044 rows=2 loops=19974)
                 Hash Cond: (app_author.id = app_article_authors.author_id)
                 ->  Seq Scan on app_author  (cost=0.00..8.59 rows=359 width=20) (actual time=0.000..0.018 rows=360 loops=19974)
                 ->  Hash  (cost=4.32..4.32 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=19974)
                       Buckets: 1024  Batches: 1  Memory Usage: 1kB
                       ->  Index Only Scan using app_article_compose_article_id_41aa189e1ccd7ab2_uniq on app_article_authors  (cost=0.29..4.32 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=19974)
                             Index Cond: (article_id = app_article.id)
                             Heap Fetches: 24458
   SubPlan 2
     ->  Aggregate  (cost=7.67..7.68 rows=1 width=21) (actual time=0.017..0.018 rows=1 loops=19974)
           ->  Hash Join  (cost=4.32..7.66 rows=1 width=21) (actual time=0.008..0.015 rows=1 loops=19974)
                 Hash Cond: (app_publisher.id = app_article_publishers.publisher_id)
                 ->  Seq Scan on app_publisher  (cost=0.00..2.97 rows=97 width=25) (actual time=0.001..0.005 rows=98 loops=19920)
                 ->  Hash  (cost=4.30..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=19974)
                       Buckets: 1024  Batches: 1  Memory Usage: 1kB
                       ->  Index Only Scan using app_article_publish_article_id_589a8464a971fbb0_uniq on app_article_publishers  (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=19974)
                             Index Cond: (article_id = app_article.id)
                             Heap Fetches: 13032
 Total runtime: 1308.972 ms
(22 rows)

Exporting m2m data for this table with around 20 thousand entries requires close to one and a half seconds on a server with 32GB of RAM. We want to reduce that.

Specifically, the sequence scans in the analysis caught my eye. In a very basic and general assumption, sequence scans are only acceptable for small tables. They are a good starting point when trying to figure out why a certain query takes too long.

First, make sure that the table has indeed an index that could be used instead of the sequence scan. This is the case for foreign key relations by default. So, an index is there but the query planner still estimates, not using it would be faster.

Second, before starting to change the query or the DB configuration we should make sure that without sequence scan the query is indeed faster. This can be done by setting the property „enable_seqscan“ to „off“.

# SQL
SET enable_seqscan=off;
SET

# run the same explain query as above...
                                                                                                                                                                                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on app_article  (cost=10000000000.00..10000667993.75 rows=19955 width=21) (actual time=0.090..261.190 rows=19974 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=20.94..20.95 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=19974)
           ->  Nested Loop  (cost=0.56..20.93 rows=2 width=16) (actual time=0.002..0.005 rows=2 loops=19974)
                 ->  Index Only Scan using app_article_compose_article_id_41aa189e1ccd7ab2_uniq on app_article_authors  (cost=0.29..4.32 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=19974)
                       Index Cond: (article_id = app_article.id)
                       Heap Fetches: 24458
                 ->  Index Scan using app_author_pkey on app_author  (cost=0.27..8.29 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=41106)
                       Index Cond: (id = app_article_authors.author_id)
   SubPlan 2
     ->  Aggregate  (cost=12.48..12.49 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=19974)
           ->  Nested Loop  (cost=0.43..12.48 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=19974)
                 ->  Index Only Scan using app_article_publish_article_id_589a8464a971fbb0_uniq on app_article_publishers  (cost=0.29..4.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=19974)
                       Index Cond: (article_id = app_article.id)
                       Heap Fetches: 13032
                 ->  Index Scan using app_publisher_pkey on app_publisher  (cost=0.14..8.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=23914)
                       Index Cond: (id = app_article_publishers.publisher_id)
 Total runtime: 262.514 ms
(18 rows)

This is obviously *much* faster than before.

Now that we have made sure that using the index is indeed faster than the sequence scan, we have to give PostgreSQL enough information to make the query planner not choose the sequence scan in this case. How should we do that?

Setting „enable_seqscan“ to „off“ in general is not recommended. Also, we still wouldn’t know why the query planner chose the sequence scan, and it would feel like a hack.

As even „vacuum analyze“ is not helping the query planner to choose differently, this has obviously another reason than data statistics.

As it turns out, PostgreSQL is by default setup to work with very low memory. Most servers however come with lots of RAM (as does ours). While sequence scans will work better than index lookup with lower RAM, the more RAM there is, the faster index lookup will be.

The two settings that tell PostgreSQL that there is more RAM and that index lookup is faster are:

effective_cache_size = 4GB  # depends on the size of the data and what part of it is queried
random_page_cost=1.5  # tells postgres that disk IO (which is now covered by the RAM Cache) is less costly, this is relative to seq_page_cost

Increasing these settings results in the following query plan:

# SQL
SET enable_seqscan=on;
SET

# run the same explain query as above...

                                                                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on app_article  (cost=0.00..268893.75 rows=19955 width=21) (actual time=0.069..249.385 rows=19974 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=8.44..8.45 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=19974)
           ->  Nested Loop  (cost=0.56..8.43 rows=2 width=16) (actual time=0.002..0.004 rows=2 loops=19974)
                 ->  Index Only Scan using app_article_compose_article_id_41aa189e1ccd7ab2_uniq on app_article_authors  (cost=0.29..1.82 rows=2 width=4) (actual time=0.001..0.001 rows=2 loops=19974)
                       Index Cond: (article_id = app_article.id)
                       Heap Fetches: 24458
                 ->  Index Scan using app_author_pkey on app_author  (cost=0.27..3.29 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=41106)
                       Index Cond: (id = app_article_authors.author_id)
   SubPlan 2
     ->  Aggregate  (cost=4.98..4.99 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=19974)
           ->  Nested Loop  (cost=0.43..4.98 rows=1 width=21) (actual time=0.002..0.003 rows=1 loops=19974)
                 ->  Index Only Scan using app_article_publish_article_id_589a8464a971fbb0_uniq on app_article_publishers  (cost=0.29..1.81 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=19974)
                       Index Cond: (article_id = app_article.id)
                       Heap Fetches: 13032
                 ->  Index Scan using app_publisher_pkey on app_publisher  (cost=0.14..3.16 rows=1 width=25) (actual time=0.001..0.001 rows=1 loops=23914)
                       Index Cond: (id = app_article_publishers.publisher_id)
 Total runtime: 250.607 ms
(18 rows)

And this is even faster than turning „enable_seqscan“ off.

Read more on „effective_cache_size“ and „random_page_cost“ at PostgreSQL’s documentation on query planning.

Further reading: Tuning Django ORM (part 1): Text Queries

[fblike style=“button_count“ showfaces=“false“ width=“90″ verb=“like“ font=“arial“ float=“left“] [fbshare type=“button“ float=“left“] [google_plusone size=“medium“ float=“left“] [twitter style=“horizontal“ float=“left“ lang=“de“] [linkedin_share style=“right“ float=“left“]

Verwandte Beiträge

Leave a comment

3wQzNl

Bitte geben Sie den Text vor: