Slow query using Django and PostgreSQL with > 40 million rows

Question

I'm working with a model (table) with more than 40 million rows and queries are very slow (> 2 min), using PostgreSQL.

The models:

class ModelA(...):
    source = models.ForeignKey(ModelB, related_name='source', ...)
    target = models.ForeignKey(ModelB, related_name='target', ...)

class ModelB(...):
    c = models.ForeignKey(ModelC, ...)
    ...

The Django filter:

ModelA.objects.filter(source__isnull=True, target__c=my_c)

The query:

SELECT "model_a"."id", ... 
FROM "model_a" 
INNER JOIN "model_b" T3 ON ("model_a"."target_id" = T3."id") 
WHERE ("model_a"."removed" = False AND "model_a"."source_id" IS NULL AND T3."c_id" = 389)

Explain query:

                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1001.26..2498957.37 rows=12277 width=71)
   Workers Planned: 3
   ->  Nested Loop  (cost=1.26..2496729.67 rows=3960 width=71)
         ->  Parallel Index Scan using model_b_model_c_removed_filename on model_b t3  (cost=0.69..2481771.88 rows=5907 width=4)
               Index Cond: (c_id = 389)
         ->  Index Scan using model_a_source_target_idx on model_a  (cost=0.56..2.52 rows=1 width=71)
               Index Cond: (target_id = t3.id)
               Filter: (NOT removed)
(8 rows)

How can I optimize it?

I am having optimization problems and I would like to know different solutions, etc.

Run `EXPLAIN YOU QUERY` and put query execution plane here – Slava Rozhnev Jun 09 '20 at 19:39 — Slava Rozhnev, Jun 09 '20 at 19:39
@SlavaRozhnev The `EXPLAIN` was added. Thx – Alejandroid Jun 11 '20 at 16:48 — Alejandroid, Jun 11 '20 at 16:48

score 1 · Answer 1 · answered Jun 09 '20 at 14:27

1

In Django you can use select_related and prefetch_related to optimized your queries.
You can add indexing into your models.
Use Redis or Memcached for caching layer to optimized read access.

What's the difference between select_related and prefetch_related in Django ORM? Stack over flow

select_related Django official doc

prefetch_related Django official doc

Solving Performance Problems in the Django ORM

Django Database Optimization Tips

answered Jun 09 '20 at 14:27

Muhammad Faizan Fareed

3,298
34
30

1

I try with `select_related` and `prefetch_related` but still slow. Thanks for all the documentation – Alejandroid Jun 11 '20 at 16:49
You can also try with NoSql databases for fast data access. Multiple techniques are used to tackle this kind of task. One factor is system specs. – Muhammad Faizan Fareed Jun 11 '20 at 18:25

Slow query using Django and PostgreSQL with > 40 million rows

1 Answers1