PostgreSQL queries slow after upgrading django project from 2.X to 3.2

We just upgraded our project from Django 2.x to 3.2 and are experiencing queries that are much slower. We are not quite sure what is handled differently, but if we EXPLAIN ANALYZE some query on a local database before the migration and one after, we see very different results with regards to time.

Example Query:

SELECT SUM("journals_journalmutation"."amount") AS "sum" 
FROM "journals_journalmutation" INNER JOIN "ledger_ledgeraccount" ON ("journals_journalmutation"."ledger_account_id" = "ledger_ledgeraccount"."id") 
INNER JOIN "journals_purchasejournalentryline" ON ("journals_journalmutation"."purchase_journal_entry_line_id" = "journals_purchasejournalentryline"."id") 
WHERE ("ledger_ledgeraccount"."master_ledger_account_id" IN (1611, 1612, 1613) 
AND "journals_purchasejournalentryline"."journal_entry_id" = 370464 
AND "journals_journalmutation"."credit_debit" = 'DEBIT' AND "ledger_ledgeraccount"."master_ledger_account_id" IN (1611, 1612, 1613))

Here is the result of EXPLAIN ANALYZE on that query on the database before migration:

Aggregate (cost=19.83..19.84 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=1)
-> Nested Loop (cost=1.28..19.82 rows=1 width=6) (actual time=0.006..0.007 rows=0 loops=1)
-> Nested Loop (cost=0.85..17.70 rows=4 width=10) (actual time=0.006..0.006 rows=0 loops=1)
-> Index Scan using journals_purchasejournalentryline_ea982348 on journals_purchasejournalentryline (cost=0.42..8.44 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=1)
Index Cond: (journal_entry_id = 370464)
-> Index Scan using pjel_idx on journals_journalmutation (cost=0.43..9.25 rows=1 width=14) (never executed)
Index Cond: (purchase_journal_entry_line_id = journals_purchasejournalentryline.id)
Filter: ((credit_debit)::text = 'DEBIT'::text)
-> Index Scan using ledger_ledgeraccount_pkey on ledger_ledgeraccount (cost=0.42..0.53 rows=1 width=4) (never executed)
Index Cond: (id = journals_journalmutation.ledger_account_id)
Filter: ((master_ledger_account_id = ANY ('{1611,1612,1613}'::integer[])) AND (master_ledger_account_id = ANY ('{1611,1612,1613}'::integer[])))
Planning Time: 0.345 ms
Execution Time: 0.051 ms

And here is the one after applying the database migrations of the django 3.2 upgrade

Aggregate (cost=50703.17..50703.18 rows=1 width=32) (actual time=1511.598..1523.303 rows=1 loops=1)
-> Gather (cost=1356.06..50703.17 rows=1 width=6) (actual time=1511.581..1523.284 rows=0 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=356.06..49703.07 rows=1 width=6) (actual time=1504.474..1504.477 rows=0 loops=3)
-> Hash Join (cost=355.64..49674.35 rows=62 width=14) (actual time=33.586..1131.691 rows=79231 loops=3)
Hash Cond: (journals_journalmutation.ledger_account_id = ledger_ledgeraccount.id)
-> Parallel Seq Scan on journals_journalmutation (cost=0.00..47774.98 rows=588080 width=22) (actual time=0.838..940.875 rows=467413 loops=3)
Filter: ((credit_debit)::text = 'DEBIT'::text)
Rows Removed by Filter: 455850
-> Hash (cost=354.44..354.44 rows=96 width=8) (actual time=32.445..32.446 rows=9330 loops=3)
Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 493kB
-> Index Scan using ledger_ledgeraccount_ca6d702e on ledger_ledgeraccount (cost=0.42..354.44 rows=96 width=8) (actual time=0.102..29.208 rows=9330 loops=3)
Index Cond: ((master_ledger_account_id = ANY ('{1611,1612,1613}'::integer[])) AND (master_ledger_account_id = ANY ('{1611,1612,1613}'::integer[])))
-> Index Scan using journals_purchasejournalentryline_pkey on journals_purchasejournalentryline (cost=0.42..0.46 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=237694)
Index Cond: (id = journals_journalmutation.purchase_journal_entry_line_id)
Filter: (journal_entry_id = 370464)
Rows Removed by Filter: 1
Planning Time: 16.547 ms
Execution Time: 1523.460 ms

Can anyone tell me what the major difference is here and what in the upgrade to Django 3.2 might have caused this?

Back to Top