explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QHve

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 31.168 ↑ 1.0 4 1

Sort (cost=8,591.74..8,591.75 rows=4 width=576) (actual time=31.167..31.168 rows=4 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 27kB
2. 0.031 31.161 ↑ 1.0 4 1

WindowAgg (cost=8,193.79..8,591.70 rows=4 width=576) (actual time=31.141..31.161 rows=4 loops=1)

3. 0.011 31.130 ↑ 1.0 4 1

Nested Loop (cost=8,193.79..8,591.65 rows=4 width=560) (actual time=28.845..31.130 rows=4 loops=1)

  • Join Filter: (dcg.chat_group_id = chat_group.id)
4. 0.850 31.095 ↑ 24.8 4 1

Hash Join (cost=8,193.50..8,323.64 rows=99 width=44) (actual time=28.830..31.095 rows=4 loops=1)

  • Hash Cond: (cm.chat_group_id = dcg.chat_group_id)
5. 6.870 30.023 ↓ 1.8 9,129 1

HashAggregate (cost=8,137.50..8,189.16 rows=5,166 width=20) (actual time=27.903..30.023 rows=9,129 loops=1)

  • Group Key: cm.chat_group_id
6. 3.760 23.153 ↓ 2.0 10,082 1

Hash Left Join (cost=195.95..8,098.76 rows=5,166 width=12) (actual time=1.484..23.153 rows=10,082 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
7. 18.605 19.382 ↑ 1.0 10,082 1

Bitmap Heap Scan on chat_message cm (cost=192.51..8,056.56 rows=10,333 width=12) (actual time=1.465..19.382 rows=10,082 loops=1)

  • Recheck Cond: (sender_id = 54)
  • Heap Blocks: exact=5,486
8. 0.777 0.777 ↑ 1.0 10,082 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..189.92 rows=10,333 width=0) (actual time=0.777..0.777 rows=10,082 loops=1)

  • Index Cond: (sender_id = 54)
9. 0.001 0.011 ↑ 1.0 1 1

Hash (cost=3.43..3.43 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.001 0.010 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.41..3.43 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=1)

11. 0.001 0.009 ↑ 1.0 1 1

Limit (cost=0.41..3.42 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=1)

12. 0.008 0.008 ↑ 477.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,432.67 rows=477 width=12) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (account_id = 54)
13. 0.005 0.222 ↑ 1.0 36 1

Hash (cost=55.53..55.53 rows=37 width=24) (actual time=0.222..0.222 rows=36 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
14. 0.217 0.217 ↑ 1.0 36 1

Seq Scan on direct_chat_group dcg (cost=0.00..55.53 rows=37 width=24) (actual time=0.015..0.217 rows=36 loops=1)

  • Filter: ((account_id_one = 54) OR (account_id_two = 54))
  • Rows Removed by Filter: 1,933
15. 0.024 0.024 ↑ 1.0 1 4

Index Scan using chat_group_pkey on chat_group (cost=0.29..2.69 rows=1 width=516) (actual time=0.006..0.006 rows=1 loops=4)

  • Index Cond: (id = cm.chat_group_id)
Planning time : 3.811 ms
Execution time : 31.240 ms