explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3QJT

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

Limit (cost=11,759.46..11,759.52 rows=25 width=576) (actual time=76.411..76.415 rows=25 loops=1)

2.          

CTE chat_messages_cte

3. 5.843 18.591 ↓ 1.9 9,015 1

HashAggregate (cost=8,112.53..8,159.87 rows=4,734 width=20) (actual time=16.786..18.591 rows=9,015 loops=1)

  • Group Key: cm.chat_group_id
4. 3.753 12.748 ↓ 2.1 9,985 1

Hash Left Join (cost=177.23..8,077.02 rows=4,734 width=12) (actual time=1.573..12.748 rows=9,985 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
5. 8.157 8.976 ↓ 1.1 9,985 1

Bitmap Heap Scan on chat_message cm (cost=173.79..8,038.08 rows=9,467 width=12) (actual time=1.546..8.976 rows=9,985 loops=1)

  • Recheck Cond: (sender_id = 12)
  • Heap Blocks: exact=5,423
6. 0.819 0.819 ↓ 1.1 9,985 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..171.43 rows=9,467 width=0) (actual time=0.818..0.819 rows=9,985 loops=1)

  • Index Cond: (sender_id = 12)
7. 0.003 0.019 ↑ 1.0 1 1

Hash (cost=3.42..3.42 rows=1 width=12) (actual time=0.019..0.019 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.002 0.016 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.41..3.42 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=1)

9. 0.001 0.014 ↑ 1.0 1 1

Limit (cost=0.41..3.41 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=1)

10. 0.013 0.013 ↑ 478.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,432.69 rows=478 width=12) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (account_id = 12)
11. 0.012 76.411 ↑ 1.3 25 1

Sort (cost=3,599.59..3,599.67 rows=33 width=576) (actual time=76.410..76.411 rows=25 loops=1)

  • Sort Key: chat_messages_cte.created
  • Sort Method: quicksort Memory: 31kB
12. 0.046 76.399 ↑ 1.0 33 1

WindowAgg (cost=7.96..3,598.76 rows=33 width=576) (actual time=76.388..76.399 rows=33 loops=1)

13. 0.067 76.353 ↑ 1.0 33 1

Nested Loop Left Join (cost=7.96..3,598.34 rows=33 width=560) (actual time=21.655..76.353 rows=33 loops=1)

14. 0.287 0.287 ↑ 1.0 33 1

Seq Scan on direct_chat_group (cost=0.00..55.53 rows=33 width=24) (actual time=0.022..0.287 rows=33 loops=1)

  • Filter: ((account_id_one = 12) OR (account_id_two = 12))
  • Rows Removed by Filter: 1,936
15. 28.413 75.999 ↓ 0.0 0 33

Hash Join (cost=7.96..115.06 rows=1 width=536) (actual time=2.114..2.303 rows=0 loops=33)

  • Hash Cond: (chat_messages_cte.chat_group_id = chat_group.id)
16. 47.388 47.388 ↓ 1.9 9,015 33

CTE Scan on chat_messages_cte (cost=0.00..94.68 rows=4,734 width=20) (actual time=0.509..1.436 rows=9,015 loops=33)

17. 0.033 0.198 ↑ 1.0 1 33

Hash (cost=7.94..7.94 rows=1 width=516) (actual time=0.006..0.006 rows=1 loops=33)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.165 0.165 ↑ 1.0 1 33

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

  • Index Cond: (id = direct_chat_group.chat_group_id)
Planning time : 0.528 ms
Execution time : 76.605 ms