explain.depesz.com

PostgreSQL's explain analyze made readable

Result: amAE

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2,762.303 ↑ 511.8 4 1

Sort (cost=114,890.52..114,895.64 rows=2,047 width=558) (actual time=2,762.303..2,762.303 rows=4 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 30kB
  • Functions: 44
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 4.472 ms, Inlining 0.000 ms, Optimization 1.351 ms, Emission 19.590 ms, Total 25.413 ms
2. 0.035 2,762.294 ↑ 511.8 4 1

WindowAgg (cost=111,014.84..114,777.94 rows=2,047 width=558) (actual time=2,762.291..2,762.294 rows=4 loops=1)

3. 0.031 2,762.259 ↑ 511.8 4 1

Hash Semi Join (cost=111,014.84..114,752.36 rows=2,047 width=542) (actual time=2,741.816..2,762.259 rows=4 loops=1)

  • Hash Cond: (chat_group.id = direct_chat_group.chat_group_id)
4. 3.538 2,733.162 ↑ 759.6 7 1

Hash Join (cost=110,309.75..114,002.86 rows=5,317 width=542) (actual time=2,711.718..2,733.162 rows=7 loops=1)

  • Hash Cond: (chat_group.id = cm.chat_group_id)
5. 19.506 24.365 ↓ 1.0 18,257 1

Bitmap Heap Scan on chat_group (cost=185.48..3,830.66 rows=18,255 width=522) (actual time=5.208..24.365 rows=18,257 loops=1)

  • Recheck Cond: (member_ids @> '{4}'::integer[])
  • Heap Blocks: exact=3,388
6. 4.859 4.859 ↓ 1.0 18,257 1

Bitmap Index Scan on chat_group_members_idx (cost=0.00..180.91 rows=18,255 width=0) (actual time=4.859..4.859 rows=18,257 loops=1)

  • Index Cond: (member_ids @> '{4}'::integer[])
7. 0.014 2,705.259 ↑ 766.4 19 1

Hash (cost=109,942.25..109,942.25 rows=14,562 width=20) (actual time=2,705.259..2,705.259 rows=19 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 129kB
8. 44.771 2,705.245 ↑ 766.4 19 1

HashAggregate (cost=109,651.01..109,796.63 rows=14,562 width=20) (actual time=2,705.170..2,705.245 rows=19 loops=1)

  • Group Key: cm.chat_group_id
9. 61.429 2,660.474 ↓ 2.0 39,122 1

Hash Left Join (cost=710.52..109,505.07 rows=19,459 width=12) (actual time=19.937..2,660.474 rows=39,122 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
10. 2,584.751 2,599.003 ↓ 1.0 39,122 1

Bitmap Heap Scan on chat_message cm (cost=706.18..109,354.76 rows=38,918 width=12) (actual time=19.884..2,599.003 rows=39,122 loops=1)

  • Recheck Cond: (sender_id = 4)
  • Heap Blocks: exact=37,305
11. 14.252 14.252 ↓ 1.0 39,141 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..696.45 rows=38,918 width=0) (actual time=14.252..14.252 rows=39,141 loops=1)

  • Index Cond: (sender_id = 4)
12. 0.003 0.042 ↑ 1.0 1 1

Hash (cost=4.33..4.33 rows=1 width=12) (actual time=0.042..0.042 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.001 0.039 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.56..4.33 rows=1 width=12) (actual time=0.039..0.039 rows=1 loops=1)

14. 0.009 0.038 ↑ 1.0 1 1

Limit (cost=0.56..4.32 rows=1 width=12) (actual time=0.038..0.038 rows=1 loops=1)

15. 0.029 0.029 ↑ 32,093.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.56..120,604.93 rows=32,093 width=12) (actual time=0.029..0.029 rows=1 loops=1)

  • Index Cond: (account_id = 4)
16. 4.119 29.066 ↑ 1.0 24,404 1

Hash (cost=400.04..400.04 rows=24,404 width=4) (actual time=29.066..29.066 rows=24,404 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,114kB
17. 24.947 24.947 ↑ 1.0 24,404 1

Seq Scan on direct_chat_group (cost=0.00..400.04 rows=24,404 width=4) (actual time=21.321..24.947 rows=24,404 loops=1)

Execution time : 2,767.452 ms