explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e5fPi

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

Limit (cost=4,347.93..4,347.99 rows=25 width=574) (actual time=9.647..9.652 rows=25 loops=1)

2. 0.023 9.648 ↑ 1.7 25 1

Sort (cost=4,347.93..4,348.04 rows=42 width=574) (actual time=9.646..9.648 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: quicksort Memory: 51kB
3. 0.089 9.625 ↑ 1.0 42 1

WindowAgg (cost=5.31..4,346.80 rows=42 width=574) (actual time=9.604..9.625 rows=42 loops=1)

4. 0.076 9.536 ↑ 1.0 42 1

Nested Loop Left Join (cost=5.31..4,346.17 rows=42 width=558) (actual time=0.351..9.536 rows=42 loops=1)

5. 0.072 3.202 ↑ 1.0 42 1

Nested Loop Left Join (cost=0.29..371.45 rows=42 width=538) (actual time=0.159..3.202 rows=42 loops=1)

6. 0.358 0.358 ↑ 1.0 42 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=42 width=24) (actual time=0.022..0.358 rows=42 loops=1)

  • Filter: ((account_id_one = 52) OR (account_id_two = 52))
  • Rows Removed by Filter: 1,927
7. 2.772 2.772 ↑ 1.0 1 42

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.83 rows=1 width=514) (actual time=0.066..0.066 rows=1 loops=42)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.000 6.258 ↑ 1.0 1 42

Subquery Scan on chat_messages_cte (cost=5.02..94.63 rows=1 width=20) (actual time=0.149..0.149 rows=1 loops=42)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.294 6.258 ↑ 10.0 1 42

GroupAggregate (cost=5.02..94.50 rows=10 width=20) (actual time=0.149..0.149 rows=1 loops=42)

  • Group Key: cm.chat_group_id
10. 0.380 5.964 ↓ 1.8 18 42

Nested Loop Left Join (cost=5.02..94.33 rows=10 width=12) (actual time=0.089..0.142 rows=18 loops=42)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 2
11. 2.058 4.746 ↑ 1.1 20 42

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.069..0.113 rows=20 loops=42)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=837
12. 2.688 2.688 ↑ 1.1 20 42

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

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.292 0.838 ↓ 0.0 0 838

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

14. 0.042 0.546 ↓ 0.0 0 42

Subquery Scan on cgr (cost=0.43..8.47 rows=1 width=12) (actual time=0.013..0.013 rows=0 loops=42)

15. 0.042 0.504 ↓ 0.0 0 42

GroupAggregate (cost=0.43..8.46 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=42)

  • Group Key: chat_group_read.chat_group_id
16. 0.462 0.462 ↓ 0.0 0 42

Index Only Scan using chat_group_read_2 on chat_group_read (cost=0.43..8.45 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=42)

  • Index Cond: ((account_id = 52) AND (chat_group_id = chat_group.id))
  • Heap Fetches: 12
Planning time : 0.501 ms
Execution time : 9.736 ms