explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vs9Xg

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

Limit (cost=10,094.87..10,094.94 rows=25 width=574) (actual time=44.691..44.695 rows=25 loops=1)

2. 0.020 44.692 ↑ 1.5 25 1

Sort (cost=10,094.87..10,094.97 rows=37 width=574) (actual time=44.690..44.692 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: quicksort Memory: 56kB
3. 0.088 44.672 ↑ 1.0 37 1

WindowAgg (cost=5.31..10,093.91 rows=37 width=574) (actual time=44.654..44.672 rows=37 loops=1)

4. 0.045 44.584 ↑ 1.0 37 1

Nested Loop Left Join (cost=5.31..10,093.35 rows=37 width=558) (actual time=1.397..44.584 rows=37 loops=1)

5. 0.054 3.136 ↑ 1.0 37 1

Nested Loop Left Join (cost=0.29..333.91 rows=37 width=538) (actual time=0.138..3.136 rows=37 loops=1)

6. 0.307 0.307 ↑ 1.0 37 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=37 width=24) (actual time=0.012..0.307 rows=37 loops=1)

  • Filter: ((account_id_one = 67) OR (account_id_two = 67))
  • Rows Removed by Filter: 1,932
7. 2.775 2.775 ↑ 1.0 1 37

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.88 rows=1 width=514) (actual time=0.075..0.075 rows=1 loops=37)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.037 41.403 ↑ 1.0 1 37

Subquery Scan on chat_messages_cte (cost=5.02..263.76 rows=1 width=20) (actual time=1.119..1.119 rows=1 loops=37)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.370 41.366 ↑ 10.0 1 37

GroupAggregate (cost=5.02..263.63 rows=10 width=20) (actual time=1.118..1.118 rows=1 loops=37)

  • Group Key: cm.chat_group_id
10. 0.890 40.996 ↓ 1.9 19 37

Nested Loop Left Join (cost=5.02..263.46 rows=10 width=12) (actual time=0.247..1.108 rows=19 loops=37)

  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 1
11. 31.709 34.891 ↑ 1.1 20 37

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.137..0.943 rows=20 loops=37)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=745
12. 3.182 3.182 ↑ 1.1 20 37

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

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.000 5.215 ↓ 0.0 0 745

Subquery Scan on cgr (cost=0.43..8.46 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=745)

  • Filter: ((cgr.chat_group_id = chat_group.id) AND (cm.chat_group_id = cgr.chat_group_id))
14. 0.745 5.215 ↓ 0.0 0 745

Limit (cost=0.43..8.45 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=745)

15. 4.470 4.470 ↓ 0.0 0 745

Index Only Scan Backward using chat_group_read_main on chat_group_read (cost=0.43..8.45 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=745)

  • Index Cond: ((chat_group_id = cm.chat_group_id) AND (account_id = 67))
  • Heap Fetches: 59
Planning time : 0.367 ms
Execution time : 44.759 ms