How to improve performance of a Postgres function with multiple JOIN – Postgresql

Postgres version: 13.2

We have a complex query to get a list of decks in production and we experience slow response time.

Here is the content of the function. I have hardcoded the 4 arguments of the function to get a detailed query plan

SELECT community_deck.approved_at,
        community_deck.approval_status,
        community_deck.is_public,
        community_deck.owner_sub,
        decks.id,
        decks.title,
        decks.owner,
        decks.share_id,
        decks.objective,
        decks.description,
        decks.updated_at,
        decks.frontend_id,
        af.field,
        users.avatar,
        users.quote,
        users.nickname,
        users.picture,
        users.profile_picture,
        ud.background_color,
        ud.text_color,
        count(DISTINCT dc.card_id) as number_of_cards,
        count(DISTINCT dr.user_id) as number_of_likes,
        count(DISTINCT dd.user_id) as number_of_unique_downloads
 from community_deck
          left join decks on decks.id = community_deck.deck_id
          left join decks_cards dc on decks.id = dc.deck_id
          left join deck_reaction dr on decks.id = dr.deck_id
          left join academic_fields af on af.id = decks.category
          left join users on users.sub = decks.owner
          left join user_deck ud on decks.id = ud.deck_id AND ud.user_id = decks.owner
          left join deck_download dd on decks.id = dd.deck_id
 WHERE community_deck.is_public = true
   AND community_deck.approval_status = 'approved'
   AND (null is null OR af.field = null)
 group by decks.id, af.field, users.avatar, users.quote, users.nickname, users.picture,
          users.profile_picture,
          ud.background_color, ud.text_color, decks.created_at, community_deck.approved_at,
          community_deck.approval_status, community_deck.is_public, community_deck.owner_sub
 ORDER BY approved_at DESC;

There might be multiple factors as why the reponse time is slow but I wanted to know if a query like this could become problematic and if there were obvious improvements that could be added.

I ran a query plan using explain (analyze, buffers) SELECT and here is the result I got:

GroupAggregate  (cost=314.33..396.91 rows=1573 width=410) (actual time=517.521..945.153 rows=49 loops=1)
"  Group Key: community_deck.approved_at, decks.id, af.field, users.avatar, users.quote, users.nickname, users.picture, users.profile_picture, ud.background_color, ud.text_color, community_deck.approval_status, community_deck.is_public, community_deck.owner_sub"
"  Buffers: shared hit=14643, temp read=5777 written=5793"
  ->  Sort  (cost=314.33..318.26 rows=1573 width=474) (actual time=517.373..770.206 rows=81653 loops=1)
"        Sort Key: community_deck.approved_at DESC, decks.id, af.field, users.avatar, users.quote, users.nickname, users.picture, users.profile_picture, ud.background_color, ud.text_color, community_deck.is_public, community_deck.owner_sub"
        Sort Method: external merge  Disk: 39152kB
"        Buffers: shared hit=14643, temp read=5777 written=5793"
        ->  Nested Loop Left Join  (cost=102.01..230.81 rows=1573 width=474) (actual time=3.146..78.871 rows=81653 loops=1)
              Buffers: shared hit=14629
              ->  Nested Loop Left Join  (cost=101.72..140.56 rows=42 width=470) (actual time=3.111..19.517 rows=801 loops=1)
                    Buffers: shared hit=4874
                    ->  Nested Loop Left Join  (cost=101.44..124.22 rows=42 width=457) (actual time=3.058..15.053 rows=801 loops=1)
                          Buffers: shared hit=2471
                          ->  Hash Left Join  (cost=101.16..106.63 rows=42 width=303) (actual time=2.998..5.190 rows=801 loops=1)
                                Hash Cond: (decks.category = af.id)
                                Buffers: shared hit=68
                                ->  Hash Right Join  (cost=99.56..104.91 rows=42 width=307) (actual time=2.873..4.179 rows=801 loops=1)
                                      Hash Cond: (dd.deck_id = decks.id)
                                      Buffers: shared hit=64
                                      ->  Seq Scan on deck_download dd  (cost=0.00..4.69 rows=169 width=46) (actual time=0.011..0.163 rows=194 loops=1)
                                            Buffers: shared hit=3
                                      ->  Hash  (cost=99.03..99.03 rows=42 width=265) (actual time=2.844..2.851 rows=139 loops=1)
                                            Buckets: 1024  Batches: 1  Memory Usage: 50kB
                                            Buffers: shared hit=61
                                            ->  Hash Right Join  (cost=95.29..99.03 rows=42 width=265) (actual time=2.502..2.712 rows=139 loops=1)
                                                  Hash Cond: (dr.deck_id = decks.id)
                                                  Buffers: shared hit=61
                                                  ->  Seq Scan on deck_reaction dr  (cost=0.00..3.25 rows=125 width=46) (actual time=0.011..0.108 rows=138 loops=1)
                                                        Buffers: shared hit=2
                                                  ->  Hash  (cost=94.77..94.77 rows=42 width=223) (actual time=2.473..2.477 rows=49 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                                        Buffers: shared hit=59
                                                        ->  Hash Right Join  (cost=2.12..94.77 rows=42 width=223) (actual time=0.271..2.404 rows=49 loops=1)
                                                              Hash Cond: (decks.id = community_deck.deck_id)
                                                              Buffers: shared hit=59
                                                              ->  Seq Scan on decks  (cost=0.00..85.43 rows=2743 width=162) (actual time=0.010..1.763 rows=2780 loops=1)
                                                                    Buffers: shared hit=58
                                                              ->  Hash  (cost=1.60..1.60 rows=42 width=65) (actual time=0.070..0.072 rows=49 loops=1)
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 13kB
                                                                    Buffers: shared hit=1
                                                                    ->  Seq Scan on community_deck  (cost=0.00..1.60 rows=42 width=65) (actual time=0.019..0.041 rows=49 loops=1)
                                                                          Filter: (is_public AND (approval_status = 'approved'::text))
                                                                          Rows Removed by Filter: 4
                                                                          Buffers: shared hit=1
                                ->  Hash  (cost=1.27..1.27 rows=27 width=28) (actual time=0.042..0.043 rows=27 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 10kB
                                      Buffers: shared hit=1
                                      ->  Seq Scan on academic_fields af  (cost=0.00..1.27 rows=27 width=28) (actual time=0.009..0.015 rows=27 loops=1)
                                            Buffers: shared hit=1
                          ->  Index Scan using users_sub_uindex on users  (cost=0.28..0.42 rows=1 width=196) (actual time=0.011..0.011 rows=1 loops=801)
                                Index Cond: (sub = decks.owner)
                                Buffers: shared hit=2403
                    ->  Index Scan using user_deck_pkey on user_deck ud  (cost=0.28..0.39 rows=1 width=58) (actual time=0.004..0.004 rows=1 loops=801)
                          Index Cond: ((deck_id = decks.id) AND (user_id = decks.owner))
                          Buffers: shared hit=2403
              ->  Index Only Scan using decks_cards_pkey on decks_cards dc  (cost=0.29..1.72 rows=43 width=8) (actual time=0.006..0.035 rows=102 loops=801)
                    Index Cond: (deck_id = decks.id)
                    Heap Fetches: 8672
                    Buffers: shared hit=9755
Planning:
  Buffers: shared hit=589
Planning Time: 12.864 ms
Execution Time: 955.913 ms