explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0uIL

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 96.423 ↑ 1.0 25 1

Limit (cost=11,693.28..11,693.34 rows=25 width=576) (actual time=96.419..96.423 rows=25 loops=1)

2. 0.012 96.419 ↑ 1.3 25 1

Sort (cost=11,693.28..11,693.36 rows=33 width=576) (actual time=96.418..96.419 rows=25 loops=1)

  • Sort Key: (max(cm.created))
  • Sort Method: quicksort Memory: 31kB
3. 0.086 96.407 ↑ 1.0 33 1

WindowAgg (cost=8,129.30..11,692.44 rows=33 width=576) (actual time=96.396..96.407 rows=33 loops=1)

4. 0.080 96.321 ↑ 1.0 33 1

Nested Loop Left Join (cost=8,129.30..11,692.03 rows=33 width=560) (actual time=20.671..96.321 rows=33 loops=1)

5. 0.067 0.079 ↑ 1.0 33 1

Bitmap Heap Scan on direct_chat_group (cost=8.82..36.69 rows=33 width=24) (actual time=0.018..0.079 rows=33 loops=1)

  • Recheck Cond: ((account_id_one = 12) OR (account_id_two = 12))
  • Heap Blocks: exact=13
6. 0.001 0.012 ↓ 0.0 0 1

BitmapOr (cost=8.82..8.82 rows=33 width=0) (actual time=0.012..0.012 rows=0 loops=1)

7. 0.008 0.008 ↑ 1.0 18 1

Bitmap Index Scan on direct_chat_group_account_id_one_idx (cost=0.00..4.41 rows=18 width=0) (actual time=0.008..0.008 rows=18 loops=1)

  • Index Cond: (account_id_one = 12)
8. 0.003 0.003 ↑ 1.0 15 1

Bitmap Index Scan on direct_chat_group_account_id_two_idx (cost=0.00..4.39 rows=15 width=0) (actual time=0.003..0.003 rows=15 loops=1)

  • Index Cond: (account_id_two = 12)
9. 23.958 96.162 ↓ 0.0 0 33

Hash Join (cost=8,120.48..8,227.59 rows=1 width=536) (actual time=2.669..2.914 rows=0 loops=33)

  • Hash Cond: (cm.chat_group_id = chat_group.id)
10. 58.176 72.006 ↓ 1.9 9,015 33

HashAggregate (cost=8,112.53..8,159.87 rows=4,734 width=20) (actual time=0.549..2.182 rows=9,015 loops=33)

  • Group Key: cm.chat_group_id
11. 4.084 13.830 ↓ 2.1 9,985 1

Hash Left Join (cost=177.23..8,077.02 rows=4,734 width=12) (actual time=2.001..13.830 rows=9,985 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
12. 8.624 9.703 ↓ 1.1 9,985 1

Bitmap Heap Scan on chat_message cm (cost=173.79..8,038.08 rows=9,467 width=12) (actual time=1.949..9.703 rows=9,985 loops=1)

  • Recheck Cond: (sender_id = 12)
  • Heap Blocks: exact=5,423
13. 1.079 1.079 ↓ 1.1 9,985 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..171.43 rows=9,467 width=0) (actual time=1.079..1.079 rows=9,985 loops=1)

  • Index Cond: (sender_id = 12)
14. 0.004 0.043 ↑ 1.0 1 1

Hash (cost=3.42..3.42 rows=1 width=12) (actual time=0.042..0.043 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.002 0.039 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.41..3.42 rows=1 width=12) (actual time=0.038..0.039 rows=1 loops=1)

16. 0.001 0.037 ↑ 1.0 1 1

Limit (cost=0.41..3.41 rows=1 width=12) (actual time=0.037..0.037 rows=1 loops=1)

17. 0.036 0.036 ↑ 478.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,432.69 rows=478 width=12) (actual time=0.036..0.036 rows=1 loops=1)

  • Index Cond: (account_id = 12)
18. 0.033 0.198 ↑ 1.0 1 33

Hash (cost=7.94..7.94 rows=1 width=516) (actual time=0.006..0.006 rows=1 loops=33)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.165 0.165 ↑ 1.0 1 33

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.94 rows=1 width=516) (actual time=0.004..0.005 rows=1 loops=33)

  • Index Cond: (id = direct_chat_group.chat_group_id)
Planning time : 0.546 ms
Execution time : 96.517 ms