explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9MMv

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

Limit (cost=4,012.32..4,012.38 rows=25 width=574) (actual time=60.769..60.773 rows=25 loops=1)

2. 0.018 60.769 ↑ 1.6 25 1

Sort (cost=4,012.32..4,012.42 rows=41 width=574) (actual time=60.768..60.769 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: quicksort Memory: 61kB
3. 0.180 60.751 ↑ 1.0 41 1

WindowAgg (cost=5.31..4,011.22 rows=41 width=574) (actual time=60.733..60.751 rows=41 loops=1)

4. 0.114 60.571 ↑ 1.0 41 1

Nested Loop Left Join (cost=5.31..4,010.60 rows=41 width=558) (actual time=1.503..60.571 rows=41 loops=1)

5. 0.175 4.656 ↑ 1.0 41 1

Nested Loop Left Join (cost=0.29..363.14 rows=41 width=538) (actual time=0.170..4.656 rows=41 loops=1)

6. 0.340 0.340 ↑ 1.0 41 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=41 width=24) (actual time=0.015..0.340 rows=41 loops=1)

  • Filter: ((account_id_one = 34) OR (account_id_two = 34))
  • Rows Removed by Filter: 1,928
7. 4.141 4.141 ↑ 1.0 1 41

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.82 rows=1 width=514) (actual time=0.101..0.101 rows=1 loops=41)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.041 55.801 ↑ 1.0 1 41

Subquery Scan on chat_messages_cte (cost=5.02..88.95 rows=1 width=20) (actual time=1.361..1.361 rows=1 loops=41)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.533 55.760 ↑ 10.0 1 41

GroupAggregate (cost=5.02..88.83 rows=10 width=20) (actual time=1.360..1.360 rows=1 loops=41)

  • Group Key: cm.chat_group_id
10. 1.142 55.227 ↓ 2.0 20 41

Nested Loop Left Join (cost=5.02..88.65 rows=10 width=12) (actual time=0.177..1.347 rows=20 loops=41)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
11. 49.938 53.259 ↑ 1.1 20 41

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.149..1.299 rows=20 loops=41)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=825
12. 3.321 3.321 ↑ 1.1 20 41

Bitmap Index Scan on chat_message_chat_group_id (cost=0.00..4.58 rows=21 width=0) (actual time=0.081..0.081 rows=20 loops=41)

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.047 0.826 ↓ 0.0 0 826

Materialize (cost=0.43..2.80 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=826)

14. 0.000 0.779 ↓ 0.0 0 41

Subquery Scan on cgr (cost=0.43..2.80 rows=1 width=12) (actual time=0.019..0.019 rows=0 loops=41)

  • Filter: (cgr.chat_group_id = chat_group.id)
  • Rows Removed by Filter: 1
15. 0.041 0.779 ↑ 1.0 1 41

Limit (cost=0.43..2.79 rows=1 width=12) (actual time=0.019..0.019 rows=1 loops=41)

16. 0.738 0.738 ↑ 9,705.0 1 41

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.43..22,892.42 rows=9,705 width=12) (actual time=0.018..0.018 rows=1 loops=41)

  • Index Cond: (account_id = 34)
Planning time : 0.343 ms
Execution time : 60.844 ms