explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q1Up

Settings
# exclusive inclusive rows x rows loops node
1. 0.364 5.048 ↓ 1.2 234 1

Sort (cost=985.41..985.91 rows=200 width=168) (actual time=5.031..5.048 rows=234 loops=1)

  • Sort Key: user_reactions.alias_id DESC, user_reactions.type, (min(user_reactions.created_at)) DESC
  • Sort Method: quicksort Memory: 87kB
2. 0.202 4.684 ↓ 1.2 234 1

HashAggregate (cost=975.76..977.76 rows=200 width=168) (actual time=4.646..4.684 rows=234 loops=1)

  • Group Key: (CASE WHEN (user_reactions.alias_id IS NOT NULL) THEN NULL::uuid ELSE user_reactions.user_id END), (COALESCE(anonymous_aliases.username, users.username)), (COALESCE(anonymous_aliases.display_name, users.display_name)), user_reactions.ali (...)
3. 0.217 4.482 ↑ 1.0 252 1

HashAggregate (cost=965.86..968.50 rows=264 width=168) (actual time=4.433..4.482 rows=252 loops=1)

  • Group 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.display_name, users.dis (...)
4. 0.012 4.265 ↑ 1.0 252 1

Append (cost=287.62..961.24 rows=264 width=168) (actual time=2.060..4.265 rows=252 loops=1)

5. 0.066 2.244 ↓ 1.1 194 1

Hash Left Join (cost=287.62..447.74 rows=182 width=141) (actual time=2.060..2.244 rows=194 loops=1)

  • Hash Cond: (user_reactions.alias_id = anonymous_aliases.id)
6. 0.090 2.136 ↓ 1.1 194 1

Hash Join (cost=283.57..442.57 rows=182 width=180) (actual time=2.003..2.136 rows=194 loops=1)

  • Hash Cond: (user_reactions.user_id = users.id)
7. 0.079 0.125 ↓ 1.1 194 1

Bitmap Heap Scan on user_reactions (cost=6.15..162.64 rows=182 width=45) (actual time=0.057..0.125 rows=194 loops=1)

  • Recheck Cond: ((card_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid) AND (type = 'like'::text))
  • Heap Blocks: exact=47
8. 0.046 0.046 ↓ 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.046..0.046 rows=194 loops=1)

  • Index Cond: ((card_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid) AND (type = 'like'::text))
9. 1.016 1.921 ↑ 1.0 3,352 1

Hash (cost=235.52..235.52 rows=3,352 width=151) (actual time=1.921..1.921 rows=3,352 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 638kB
10. 0.905 0.905 ↑ 1.0 3,352 1

Seq Scan on users (cost=0.00..235.52 rows=3,352 width=151) (actual time=0.006..0.905 rows=3,352 loops=1)

11. 0.024 0.042 ↑ 1.0 91 1

Hash (cost=2.91..2.91 rows=91 width=115) (actual time=0.042..0.042 rows=91 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
12. 0.018 0.018 ↑ 1.0 91 1

Seq Scan on anonymous_aliases (cost=0.00..2.91 rows=91 width=115) (actual time=0.006..0.018 rows=91 loops=1)

13. 0.028 2.009 ↑ 1.4 58 1

Hash Left Join (cost=290.75..510.87 rows=82 width=168) (actual time=1.927..2.009 rows=58 loops=1)

  • Hash Cond: (cards.alias_id = anonymous_aliases_1.id)
14. 0.050 1.940 ↑ 1.4 58 1

Hash Join (cost=286.70..506.19 rows=82 width=175) (actual time=1.874..1.940 rows=58 loops=1)

  • Hash Cond: (cards.owner_id = users_1.id)
15. 0.042 0.067 ↑ 1.4 58 1

Bitmap Heap Scan on cards (cost=9.28..227.64 rows=82 width=40) (actual time=0.031..0.067 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=39
16. 0.001 0.025 ↓ 0.0 0 1

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

17. 0.011 0.011 ↑ 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.011..0.011 rows=29 loops=1)

  • Index Cond: (thread_reply_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid)
18. 0.013 0.013 ↑ 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.013..0.013 rows=60 loops=1)

  • Index Cond: (thread_root_id = '85d4dcce-d170-4374-9754-4ba3a03e206e'::uuid)
19. 0.970 1.823 ↑ 1.0 3,352 1

Hash (cost=235.52..235.52 rows=3,352 width=151) (actual time=1.823..1.823 rows=3,352 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 638kB
20. 0.853 0.853 ↑ 1.0 3,352 1

Seq Scan on users users_1 (cost=0.00..235.52 rows=3,352 width=151) (actual time=0.004..0.853 rows=3,352 loops=1)

21. 0.026 0.041 ↑ 1.0 91 1

Hash (cost=2.91..2.91 rows=91 width=115) (actual time=0.041..0.041 rows=91 loops=1)

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

Seq Scan on anonymous_aliases anonymous_aliases_1 (cost=0.00..2.91 rows=91 width=115) (actual time=0.005..0.015 rows=91 loops=1)