explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xWly

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2.355 ↓ 25.0 25 1

Limit (cost=3,236.71..3,236.72 rows=1 width=574) (actual time=2.351..2.355 rows=25 loops=1)

2. 0.018 2.352 ↓ 25.0 25 1

Sort (cost=3,236.71..3,236.72 rows=1 width=574) (actual time=2.350..2.352 rows=25 loops=1)

  • Sort Key: (GREATEST(chat_messages_cte.created, chat_group.created))
  • Sort Method: quicksort Memory: 52kB
3. 0.049 2.334 ↓ 33.0 33 1

WindowAgg (cost=5.17..3,236.70 rows=1 width=574) (actual time=2.320..2.334 rows=33 loops=1)

4. 0.025 2.285 ↓ 33.0 33 1

Nested Loop (cost=5.17..3,236.69 rows=1 width=558) (actual time=0.108..2.285 rows=33 loops=1)

  • Join Filter: (direct_chat_group.chat_group_id = chat_messages_cte.chat_group_id)
5. 0.042 0.412 ↑ 1.0 33 1

Nested Loop (cost=0.29..304.68 rows=33 width=538) (actual time=0.016..0.412 rows=33 loops=1)

6. 0.271 0.271 ↑ 1.0 33 1

Seq Scan on direct_chat_group (cost=0.00..42.53 rows=33 width=24) (actual time=0.009..0.271 rows=33 loops=1)

  • Filter: ((account_id_one = 12) OR (account_id_two = 12))
  • Rows Removed by Filter: 1,936
7. 0.099 0.099 ↑ 1.0 1 33

Index Scan using chat_group_pkey on chat_group (cost=0.29..7.94 rows=1 width=514) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: (id = direct_chat_group.chat_group_id)
8. 0.033 1.848 ↑ 1.0 1 33

Subquery Scan on chat_messages_cte (cost=4.88..88.84 rows=1 width=20) (actual time=0.056..0.056 rows=1 loops=33)

  • Filter: (chat_group.id = chat_messages_cte.chat_group_id)
9. 0.198 1.815 ↑ 10.0 1 33

GroupAggregate (cost=4.88..88.71 rows=10 width=20) (actual time=0.055..0.055 rows=1 loops=33)

  • Group Key: cm.chat_group_id
10. 0.660 1.617 ↓ 2.0 20 33

Nested Loop Left Join (cost=4.88..88.54 rows=10 width=12) (actual time=0.019..0.049 rows=20 loops=33)

  • Join Filter: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
11. 0.726 0.957 ↑ 1.1 20 33

Bitmap Heap Scan on chat_message cm (cost=4.59..85.48 rows=21 width=12) (actual time=0.010..0.029 rows=20 loops=33)

  • Recheck Cond: (chat_group.id = chat_group_id)
  • Heap Blocks: exact=673
12. 0.231 0.231 ↑ 1.1 20 33

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

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

Materialize (cost=0.29..2.69 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=673)

14. 0.000 0.165 ↓ 0.0 0 33

Subquery Scan on cgr (cost=0.29..2.68 rows=1 width=12) (actual time=0.005..0.005 rows=0 loops=33)

  • Filter: (cgr.chat_group_id = chat_group.id)
  • Rows Removed by Filter: 1
15. 0.000 0.165 ↑ 1.0 1 33

Limit (cost=0.29..2.67 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=33)

16. 0.165 0.165 ↑ 452.0 1 33

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.29..1,076.11 rows=452 width=12) (actual time=0.005..0.005 rows=1 loops=33)

  • Index Cond: (account_id = 12)
Planning time : 0.396 ms
Execution time : 2.418 ms