explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ioJr

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

Limit (cost=67,098.41..67,098.47 rows=25 width=576) (actual time=1,024.629..1,024.632 rows=25 loops=1)

2. 0.017 1,024.629 ↑ 33.1 25 1

Sort (cost=67,098.41..67,100.48 rows=828 width=576) (actual time=1,024.628..1,024.629 rows=25 loops=1)

  • Sort Key: (GREATEST((max(cm.created)), chat_group.created))
  • Sort Method: quicksort Memory: 52kB
3. 0.119 1,024.612 ↑ 25.1 33 1

WindowAgg (cost=4.42..67,075.05 rows=828 width=576) (actual time=1,024.598..1,024.612 rows=33 loops=1)

4. 3.132 1,024.493 ↑ 25.1 33 1

Merge Join (cost=4.42..67,062.63 rows=828 width=560) (actual time=17.444..1,024.493 rows=33 loops=1)

  • Merge Cond: (direct_chat_group.chat_group_id = cm.chat_group_id)
5. 0.085 1.186 ↑ 1.0 33 1

Nested Loop (cost=0.57..473.79 rows=33 width=540) (actual time=0.029..1.186 rows=33 loops=1)

6. 0.903 0.903 ↑ 1.0 33 1

Index Scan using direct_chat_group_chat_group_id_idx on direct_chat_group (cost=0.28..211.64 rows=33 width=24) (actual time=0.024..0.903 rows=33 loops=1)

  • Filter: ((account_id_one = 12) OR (account_id_two = 12))
  • Rows Removed by Filter: 1,936
7. 0.198 0.198 ↑ 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.006..0.006 rows=1 loops=33)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 238.557 1,020.175 ↓ 1.0 49,695 1

GroupAggregate (cost=3.86..65,974.64 rows=48,467 width=20) (actual time=0.055..1,020.175 rows=49,695 loops=1)

  • Group Key: cm.chat_group_id
9. 255.621 781.618 ↓ 2.0 994,014 1

Merge Left Join (cost=3.86..61,739.97 rows=500,000 width=12) (actual time=0.018..781.618 rows=994,014 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. 525.983 525.983 ↑ 1.0 994,021 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.004..525.983 rows=994,021 loops=1)

11. 0.003 0.014 ↑ 1.0 1 1

Materialize (cost=3.43..3.44 rows=1 width=12) (actual time=0.012..0.014 rows=1 loops=1)

12. 0.003 0.011 ↑ 1.0 1 1

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

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

Subquery Scan on cgr (cost=0.41..3.42 rows=1 width=12) (actual time=0.008..0.008 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.007..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)
Planning time : 0.610 ms
Execution time : 1,024.694 ms