explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3X8q

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

Limit (cost=5,468.84..5,468.90 rows=25 width=574) (actual time=19.435..19.440 rows=25 loops=1)

2. 0.057 19.436 ↑ 2.1 25 1

Sort (cost=5,468.84..5,468.97 rows=53 width=574) (actual time=19.434..19.436 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.max_created, chat_group.created))
  • Sort Method: top-N heapsort Memory: 44kB
3. 0.142 19.379 ↑ 1.0 53 1

WindowAgg (cost=5.31..5,467.35 rows=53 width=574) (actual time=19.348..19.379 rows=53 loops=1)

4. 0.081 19.237 ↑ 1.0 53 1

Nested Loop Left Join (cost=5.31..5,466.55 rows=53 width=558) (actual time=3.058..19.237 rows=53 loops=1)

5. 0.163 6.648 ↑ 1.0 53 1

Nested Loop Left Join (cost=0.29..450.83 rows=53 width=538) (actual time=2.739..6.648 rows=53 loops=1)

6. 0.390 0.390 ↑ 1.0 53 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=53 width=24) (actual time=0.024..0.390 rows=53 loops=1)

  • Filter: ((account_id_one = 11) OR (account_id_two = 11))
  • Rows Removed by Filter: 1,916
7. 6.095 6.095 ↑ 1.0 1 53

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.70 rows=1 width=514) (actual time=0.115..0.115 rows=1 loops=53)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.053 12.508 ↑ 1.0 1 53

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

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.371 12.455 ↑ 10.0 1 53

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

  • Group Key: cm.chat_group_id
10. 0.513 12.084 ↓ 1.6 16 53

Nested Loop Left Join (cost=5.02..94.33 rows=10 width=12) (actual time=0.168..0.228 rows=16 loops=53)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
  • Rows Removed by Filter: 3
11. 3.021 6.466 ↑ 1.1 19 53

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.071..0.122 rows=19 loops=53)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=1,021
12. 3.445 3.445 ↑ 1.1 19 53

Bitmap Index Scan on chat_message_chat_group_id (cost=0.00..4.58 rows=21 width=0) (actual time=0.065..0.065 rows=19 loops=53)

  • Index Cond: (chat_group_id = chat_group.id)
13. 0.388 5.105 ↓ 0.0 0 1,021

Materialize (cost=0.43..8.48 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=1,021)

14. 0.053 4.717 ↓ 0.0 0 53

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

15. 0.053 4.664 ↓ 0.0 0 53

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

  • Group Key: chat_group_read.chat_group_id
16. 4.611 4.611 ↓ 0.0 0 53

Index Only Scan using chat_group_read_main on chat_group_read (cost=0.43..8.45 rows=1 width=12) (actual time=0.086..0.087 rows=0 loops=53)

  • Index Cond: ((chat_group_id = chat_group.id) AND (account_id = 11))
  • Heap Fetches: 15
Planning time : 0.490 ms
Execution time : 19.530 ms