Slow Postgres JOIN Query

Question

I am trying to optimize the slow query that Django ORM generated. This is a many-to-many query. It takes more than 1 minute to start.

The tables have a large amount of data, but they are not huge (400 thousand rows in sp_article and 300 thousand rows in sp_article_categories)

#categories.article_set.filter(post_count__lte=50)

EXPLAIN ANALYZE SELECT * 
                  FROM "sp_article" 
            INNER JOIN "sp_article_categories" ON ("sp_article"."id" = "sp_article_categories"."article_id") 
                WHERE ("sp_article_categories"."category_id" = 1081  
                  AND "sp_article"."post_count" <= 50 )

Nested Loop  (cost=0.00..6029.01 rows=656 width=741) (actual time=0.472..25.724 rows=1266 loops=1)
  ->  Index Scan using sp_article_categories_category_id on sp_article_categories  (cost=0.00..848.82 rows=656 width=12) (actual time=0.015..1.305 rows=1408 loops=1)
        Index Cond: (category_id = 1081)
  ->  Index Scan using sp_article_pkey on sp_article  (cost=0.00..7.88 rows=1 width=729) (actual time=0.014..0.015 rows=1 loops=1408)
        Index Cond: (sp_article.id = sp_article_categories.article_id)
        Filter: (sp_article.post_count <= 50)
Total runtime: 26.536 ms

I have an index:

sp_article_categories.article_id (type: btree)
sp_article_categories.category_id
sp_article.post_count (type: btree)

Any suggestions on how I can configure this to get a request quickly?

Thank!

Answer 1

You have provided important information here - an analysis of the explanations. It does not show 1 second of runtime, although it does show 20 milliseconds. Thus, it is either an unsuccessful request or a problem elsewhere.

, . , 1 , .

, , . ( ), ( ).

- , ...

Answer 2

sp_article_categories.category_id

Answer 3

SQL , , WHERE , .

, , Django , category_id .

:

SELECT * FROM categories c
INNER JOIN articles a
    ON c.category_id = 1081
    AND c.category_id = a.category_id

category_id, .

Answer 4

*.

[] ....

Answer 5

, , .

, sp_article.id sp_article_categories.article_id . , ? , , - bigint, . . , .

! //