explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 12d7

Settings
# exclusive inclusive rows x rows loops node
1. 0.344 6.695 ↓ 1.3 1,129 1

Sort (cost=790.33..792.53 rows=881 width=78) (actual time=6.623..6.695 rows=1,129 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 207kB
2. 0.552 6.351 ↓ 1.3 1,129 1

Hash Join (cost=668.28..747.24 rows=881 width=78) (actual time=5.537..6.351 rows=1,129 loops=1)

  • Hash Cond: (chat_group.id = cm.chat_group_id)
3. 0.277 0.381 ↑ 1.0 1,782 1

Bitmap Heap Scan on chat_group (cost=25.81..100.09 rows=1,782 width=50) (actual time=0.113..0.381 rows=1,782 loops=1)

  • Recheck Cond: (member_ids @> '{1}'::integer[])
  • Heap Blocks: exact=52
4. 0.104 0.104 ↓ 1.0 1,798 1

Bitmap Index Scan on chat_group_members_idx (cost=0.00..25.37 rows=1,782 width=0) (actual time=0.104..0.104 rows=1,798 loops=1)

  • Index Cond: (member_ids @> '{1}'::integer[])
5. 0.469 5.418 ↓ 1.3 3,155 1

Hash (cost=611.59..611.59 rows=2,471 width=20) (actual time=5.418..5.418 rows=3,155 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 193kB
6. 2.140 4.949 ↓ 1.3 3,155 1

HashAggregate (cost=562.17..586.88 rows=2,471 width=20) (actual time=4.344..4.949 rows=3,155 loops=1)

  • Group Key: cm.chat_group_id
7. 1.495 2.809 ↓ 2.0 4,974 1

Hash Left Join (cost=95.31..543.63 rows=2,471 width=12) (actual time=0.227..2.809 rows=4,974 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
8. 1.124 1.300 ↓ 1.0 4,974 1

Bitmap Heap Scan on chat_message cm (cost=94.59..524.37 rows=4,942 width=12) (actual time=0.210..1.300 rows=4,974 loops=1)

  • Recheck Cond: (sender_id = 1)
  • Heap Blocks: exact=368
9. 0.176 0.176 ↓ 1.0 4,974 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..93.36 rows=4,942 width=0) (actual time=0.176..0.176 rows=4,974 loops=1)

  • Index Cond: (sender_id = 1)
10. 0.002 0.014 ↑ 1.0 1 1

Hash (cost=0.71..0.71 rows=1 width=12) (actual time=0.013..0.014 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.001 0.012 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.41..0.71 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=1)

12. 0.001 0.011 ↑ 1.0 1 1

Limit (cost=0.41..0.70 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

13. 0.010 0.010 ↑ 5,030.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,438.85 rows=5,030 width=12) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (account_id = 1)
Planning time : 0.521 ms
Execution time : 6.855 ms