explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tBYP

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,096.899 ↑ 1.0 25 1

Limit (cost=71,399.41..71,399.47 rows=25 width=576) (actual time=1,096.895..1,096.899 rows=25 loops=1)

2. 0.018 1,096.896 ↑ 1.3 25 1

Sort (cost=71,399.41..71,399.49 rows=33 width=576) (actual time=1,096.894..1,096.896 rows=25 loops=1)

  • Sort Key: (max(cm.created))
  • Sort Method: quicksort Memory: 52kB
3. 0.060 1,096.878 ↑ 1.0 33 1

WindowAgg (cost=67,102.25..71,398.58 rows=33 width=576) (actual time=1,096.865..1,096.878 rows=33 loops=1)

4. 4.144 1,096.818 ↑ 1.0 33 1

Hash Right Join (cost=67,102.25..71,398.17 rows=33 width=560) (actual time=1,067.009..1,096.818 rows=33 loops=1)

  • Hash Cond: (chat_group.id = direct_chat_group.chat_group_id)
5. 19.822 1,092.629 ↓ 1.0 49,999 1

Hash Join (cost=67,065.14..71,118.40 rows=48,467 width=536) (actual time=1,066.167..1,092.629 rows=49,999 loops=1)

  • Hash Cond: (chat_group.id = cm.chat_group_id)
6. 6.681 6.681 ↑ 1.0 50,000 1

Seq Scan on chat_group (cost=0.00..3,922.00 rows=50,000 width=516) (actual time=0.004..6.681 rows=50,000 loops=1)

7. 12.084 1,066.126 ↓ 1.0 49,999 1

Hash (cost=66,459.31..66,459.31 rows=48,467 width=20) (actual time=1,066.125..1,066.126 rows=49,999 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,052kB
8. 234.599 1,054.042 ↓ 1.0 49,999 1

GroupAggregate (cost=3.86..65,974.64 rows=48,467 width=20) (actual time=0.102..1,054.042 rows=49,999 loops=1)

  • Group Key: cm.chat_group_id
9. 264.686 819.443 ↓ 2.0 999,993 1

Merge Left Join (cost=3.86..61,739.97 rows=500,000 width=12) (actual time=0.021..819.443 rows=999,993 loops=1)

  • Merge Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 7
10. 554.742 554.742 ↑ 1.0 1,000,000 1

Index Scan using chat_message_chat_group_id on chat_message cm (cost=0.42..59,236.16 rows=1,000,000 width=12) (actual time=0.005..554.742 rows=1,000,000 loops=1)

11. 0.003 0.015 ↑ 1.0 1 1

Materialize (cost=3.43..3.44 rows=1 width=12) (actual time=0.013..0.015 rows=1 loops=1)

12. 0.003 0.012 ↑ 1.0 1 1

Sort (cost=3.43..3.44 rows=1 width=12) (actual time=0.011..0.012 rows=1 loops=1)

  • Sort Key: cgr.chat_group_id
  • Sort Method: quicksort Memory: 25kB
13. 0.001 0.009 ↑ 1.0 1 1

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

14. 0.001 0.008 ↑ 1.0 1 1

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

15. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Index Cond: (account_id = 12)
16. 0.007 0.045 ↑ 1.0 33 1

Hash (cost=36.69..36.69 rows=33 width=24) (actual time=0.045..0.045 rows=33 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.026 0.038 ↑ 1.0 33 1

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

  • Recheck Cond: ((account_id_one = 12) OR (account_id_two = 12))
  • Heap Blocks: exact=13
18. 0.000 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)

19. 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)
20. 0.004 0.004 ↑ 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.004..0.004 rows=15 loops=1)

  • Index Cond: (account_id_two = 12)
Planning time : 0.464 ms
Execution time : 1,096.975 ms