explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uoe

Settings
# exclusive inclusive rows x rows loops node
1. 1.336 6.040 ↓ 1.2 234 1

Sort (cost=1,822.42..1,822.92 rows=200 width=168) (actual time=5.976..6.040 rows=234 loops=1)

  • Sort Key: t.alias_id DESC, t.type, (min(t.created_at)) DESC
  • Sort Method: quicksort Memory: 87kB
2. 0.448 4.704 ↓ 1.2 234 1

GroupAggregate (cost=1,769.49..1,814.77 rows=200 width=168) (actual time=4.176..4.704 rows=234 loops=1)

  • Group Key: t.user_id, t.username, t.display_name, t.alias_id, t.profile_image_path, t.type
3. 0.663 4.256 ↑ 1.0 252 1

Sort (cost=1,769.49..1,770.15 rows=264 width=168) (actual time=4.167..4.256 rows=252 loops=1)

  • Sort Key: t.user_id, t.username, t.display_name, t.alias_id DESC, t.profile_image_path, t.type
  • Sort Method: quicksort Memory: 91kB
4. 0.098 3.593 ↑ 1.0 252 1

Subquery Scan on t (cost=1,700.79..1,758.87 rows=264 width=168) (actual time=3.255..3.593 rows=252 loops=1)

5. 0.181 3.495 ↑ 1.0 252 1

Unique (cost=1,700.79..1,706.07 rows=264 width=168) (actual time=3.252..3.495 rows=252 loops=1)

6. 0.579 3.314 ↑ 1.0 252 1

Sort (cost=1,700.79..1,701.45 rows=264 width=168) (actual time=3.251..3.314 rows=252 loops=1)

  • Sort Key: (CASE WHEN (user_reactions.alias_id IS NOT NULL) THEN NULL::uuid ELSE user_reactions.user_id END), user_reactions.alias_id, (COALESCE(anonymous_aliases.username, users.username)), (COALESCE(anonymous_aliases.displa (...)
  • Sort Method: quicksort Memory: 91kB
7. 0.053 2.735 ↑ 1.0 252 1

Append (cost=45.06..1,690.17 rows=264 width=168) (actual time=0.290..2.735 rows=252 loops=1)

8. 0.198 1.744 ↓ 1.1 194 1

Hash Left Join (cost=45.06..934.46 rows=182 width=141) (actual time=0.289..1.744 rows=194 loops=1)

  • Hash Cond: (user_reactions.alias_id = anonymous_aliases.id)
9. 0.348 1.419 ↓ 1.1 194 1

Nested Loop (cost=6.43..886.35 rows=182 width=180) (actual time=0.138..1.419 rows=194 loops=1)

10. 0.198 0.295 ↓ 1.1 194 1

Bitmap Heap Scan on user_reactions (cost=6.15..197.22 rows=182 width=45) (actual time=0.119..0.295 rows=194 loops=1)

  • Recheck Cond: ((card_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid) AND (type = 'like'::text))
  • Heap Blocks: exact=47
11. 0.097 0.097 ↓ 1.1 194 1

Bitmap Index Scan on user_reactions_card_id_type_idx (cost=0.00..6.11 rows=182 width=0) (actual time=0.097..0.097 rows=194 loops=1)

  • Index Cond: ((card_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid) AND (type = 'like'::text))
12. 0.776 0.776 ↑ 1.0 1 194

Index Scan using users_pkey on users (cost=0.28..3.59 rows=1 width=151) (actual time=0.004..0.004 rows=1 loops=194)

  • Index Cond: (id = user_reactions.user_id)
13. 0.080 0.127 ↑ 1.0 91 1

Hash (cost=20.20..20.20 rows=91 width=115) (actual time=0.127..0.127 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
14. 0.047 0.047 ↑ 1.0 91 1

Seq Scan on anonymous_aliases (cost=0.00..20.20 rows=91 width=115) (actual time=0.012..0.047 rows=91 loops=1)

15. 0.073 0.938 ↑ 1.4 58 1

Nested Loop (cost=303.56..702.92 rows=82 width=168) (actual time=0.462..0.938 rows=58 loops=1)

16. 0.076 0.575 ↑ 1.4 58 1

Merge Left Join (cost=303.28..310.54 rows=82 width=139) (actual time=0.452..0.575 rows=58 loops=1)

  • Merge Cond: (cards.alias_id = anonymous_aliases_1.id)
17. 0.095 0.310 ↑ 1.4 58 1

Sort (cost=280.12..280.32 rows=82 width=40) (actual time=0.282..0.310 rows=58 loops=1)

  • Sort Key: cards.alias_id
  • Sort Method: quicksort Memory: 29kB
18. 0.152 0.215 ↑ 1.4 58 1

Bitmap Heap Scan on cards (cost=9.28..277.51 rows=82 width=40) (actual time=0.088..0.215 rows=58 loops=1)

  • Recheck Cond: ((thread_reply_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid) OR (thread_root_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid))
  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=54
19. 0.001 0.063 ↓ 0.0 0 1

BitmapOr (cost=9.28..9.28 rows=89 width=0) (actual time=0.063..0.063 rows=0 loops=1)

20. 0.026 0.026 ↑ 1.0 29 1

Bitmap Index Scan on cards_thread_reply_id_idx (cost=0.00..4.50 rows=29 width=0) (actual time=0.026..0.026 rows=29 loops=1)

  • Index Cond: (thread_reply_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid)
21. 0.036 0.036 ↑ 1.0 60 1

Bitmap Index Scan on cards_thread_root_id_idx (cost=0.00..4.74 rows=60 width=0) (actual time=0.036..0.036 rows=60 loops=1)

  • Index Cond: (thread_root_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid)
22. 0.149 0.189 ↑ 1.1 82 1

Sort (cost=23.16..23.39 rows=91 width=115) (actual time=0.163..0.189 rows=82 loops=1)

  • Sort Key: anonymous_aliases_1.id
  • Sort Method: quicksort Memory: 48kB
23. 0.040 0.040 ↑ 1.0 91 1

Seq Scan on anonymous_aliases anonymous_aliases_1 (cost=0.00..20.20 rows=91 width=115) (actual time=0.010..0.040 rows=91 loops=1)

24. 0.290 0.290 ↑ 1.0 1 58

Index Scan using users_pkey on users users_1 (cost=0.28..4.59 rows=1 width=151) (actual time=0.004..0.005 rows=1 loops=58)

  • Index Cond: (id = cards.owner_id)