explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 46t

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 6.207 ↓ 1.1 11 1

Sort (cost=728.59..728.62 rows=10 width=78) (actual time=6.206..6.207 rows=11 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 26kB
2. 0.273 6.198 ↓ 1.1 11 1

Hash Join (cost=669.64..728.43 rows=10 width=78) (actual time=5.498..6.198 rows=11 loops=1)

  • Hash Cond: (cm.chat_group_id = chat_group.id)
3. 2.314 5.373 ↓ 1.3 3,155 1

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

  • Group Key: cm.chat_group_id
4. 1.602 3.059 ↓ 2.0 4,974 1

Hash Left Join (cost=95.31..543.63 rows=2,471 width=12) (actual time=0.303..3.059 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)
5. 1.192 1.445 ↓ 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.288..1.445 rows=4,974 loops=1)

  • Recheck Cond: (sender_id = 1)
  • Heap Blocks: exact=368
6. 0.253 0.253 ↓ 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.253..0.253 rows=4,974 loops=1)

  • Index Cond: (sender_id = 1)
7. 0.002 0.012 ↑ 1.0 1 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.000 0.010 ↑ 1.0 1 1

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

9. 0.001 0.010 ↑ 1.0 1 1

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

10. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

  • Index Cond: (account_id = 1)
11. 0.004 0.552 ↑ 1.1 18 1

Hash (cost=107.22..107.22 rows=20 width=54) (actual time=0.552..0.552 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
12. 0.177 0.548 ↑ 1.1 18 1

Hash Semi Join (cost=28.05..107.22 rows=20 width=54) (actual time=0.173..0.548 rows=18 loops=1)

  • Hash Cond: (chat_group.id = direct_chat_group.chat_group_id)
13. 0.249 0.353 ↑ 1.0 1,782 1

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

  • Recheck Cond: (member_ids @> '{1}'::integer[])
  • Heap Blocks: exact=52
14. 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[])
15. 0.008 0.018 ↑ 1.0 55 1

Hash (cost=1.55..1.55 rows=55 width=4) (actual time=0.018..0.018 rows=55 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
16. 0.010 0.010 ↑ 1.0 55 1

Seq Scan on direct_chat_group (cost=0.00..1.55 rows=55 width=4) (actual time=0.005..0.010 rows=55 loops=1)

Planning time : 0.470 ms
Execution time : 6.273 ms