explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vsxP

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 2,923.225 ↑ 548.5 4 1

Sort (cost=161,901.37..161,906.85 rows=2,194 width=558) (actual time=2,923.224..2,923.225 rows=4 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 27kB
  • Functions: 44
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 4.395 ms, Inlining 0.000 ms, Optimization 1.607 ms, Emission 21.663 ms, Total 27.665 ms
2. 0.041 2,923.216 ↑ 548.5 4 1

WindowAgg (cost=158,012.08..161,779.61 rows=2,194 width=558) (actual time=2,923.213..2,923.216 rows=4 loops=1)

3. 0.066 2,923.175 ↑ 548.5 4 1

Hash Semi Join (cost=158,012.08..161,752.18 rows=2,194 width=542) (actual time=2,892.606..2,923.175 rows=4 loops=1)

  • Hash Cond: (chat_group.id = direct_chat_group.chat_group_id)
4. 4.873 2,892.473 ↑ 570.0 10 1

Hash Join (cost=157,306.99..160,999.49 rows=5,700 width=542) (actual time=2,861.915..2,892.473 rows=10 loops=1)

  • Hash Cond: (chat_group.id = cm.chat_group_id)
5. 27.317 32.147 ↓ 1.0 18,220 1

Bitmap Heap Scan on chat_group (cost=185.17..3,829.85 rows=18,215 width=522) (actual time=5.269..32.147 rows=18,220 loops=1)

  • Recheck Cond: (member_ids @> '{16}'::integer[])
  • Heap Blocks: exact=3,399
6. 4.830 4.830 ↓ 1.0 18,220 1

Bitmap Index Scan on chat_group_members_idx (cost=0.00..180.61 rows=18,215 width=0) (actual time=4.829..4.830 rows=18,220 loops=1)

  • Index Cond: (member_ids @> '{16}'::integer[])
7. 0.023 2,855.453 ↑ 745.1 21 1

Hash (cost=156,926.24..156,926.24 rows=15,647 width=20) (actual time=2,855.453..2,855.453 rows=21 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 130kB
8. 48.086 2,855.430 ↑ 745.1 21 1

HashAggregate (cost=156,613.30..156,769.77 rows=15,647 width=20) (actual time=2,855.234..2,855.430 rows=21 loops=1)

  • Group Key: cm.chat_group_id
9. 64.849 2,807.344 ↓ 1.4 43,241 1

Hash Left Join (cost=1,150.26..156,376.52 rows=31,571 width=12) (actual time=22.017..2,807.344 rows=43,241 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
10. 2,726.391 2,742.454 ↑ 1.5 43,241 1

Bitmap Heap Scan on chat_message cm (cost=1,145.92..156,135.34 rows=63,142 width=12) (actual time=21.966..2,742.454 rows=43,241 loops=1)

  • Recheck Cond: (sender_id = 16)
  • Heap Blocks: exact=37,185
11. 16.063 16.063 ↑ 1.5 43,262 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..1,130.13 rows=63,142 width=0) (actual time=16.063..16.063 rows=43,262 loops=1)

  • Index Cond: (sender_id = 16)
12. 0.002 0.041 ↑ 1.0 1 1

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

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

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

14. 0.008 0.037 ↑ 1.0 1 1

Limit (cost=0.56..4.32 rows=1 width=12) (actual time=0.037..0.037 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.028..0.029 rows=1 loops=1)

  • Index Cond: (account_id = 16)
16. 3.609 30.636 ↑ 1.0 24,404 1

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

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

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

Execution time : 2,928.526 ms