explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UmdL

Settings
# exclusive inclusive rows x rows loops node
1. 0.339 11.340 ↓ 1.3 1,129 1

Sort (cost=1,291.93..1,294.13 rows=881 width=78) (actual time=11.270..11.340 rows=1,129 loops=1)

  • Sort Key: (max(cm.created)) DESC
  • Sort Method: quicksort Memory: 207kB
2. 0.538 11.001 ↓ 1.3 1,129 1

Hash Join (cost=1,169.88..1,248.84 rows=881 width=78) (actual time=10.204..11.001 rows=1,129 loops=1)

  • Hash Cond: (chat_group.id = cm.chat_group_id)
3. 0.274 0.376 ↑ 1.0 1,782 1

Bitmap Heap Scan on chat_group (cost=25.81..100.09 rows=1,782 width=50) (actual time=0.110..0.376 rows=1,782 loops=1)

  • Recheck Cond: (member_ids @> '{1}'::integer[])
  • Heap Blocks: exact=52
4. 0.102 0.102 ↓ 1.0 1,798 1

Bitmap Index Scan on chat_group_members_idx (cost=0.00..25.37 rows=1,782 width=0) (actual time=0.102..0.102 rows=1,798 loops=1)

  • Index Cond: (member_ids @> '{1}'::integer[])
5. 0.626 10.087 ↓ 1.3 3,155 1

Hash (cost=1,113.19..1,113.19 rows=2,471 width=20) (actual time=10.087..10.087 rows=3,155 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 193kB
6. 2.764 9.461 ↓ 1.3 3,155 1

HashAggregate (cost=1,063.77..1,088.48 rows=2,471 width=20) (actual time=8.713..9.461 rows=3,155 loops=1)

  • Group Key: cm.chat_group_id
7. 2.023 6.697 ↓ 2.0 4,974 1

Hash Left Join (cost=596.91..1,045.23 rows=2,471 width=12) (actual time=3.171..6.697 rows=4,974 loops=1)

  • Hash Cond: (cm.chat_group_id = cgr.chat_group_id)
  • Filter: (COALESCE((cm.created > cgr.read_time), false) IS FALSE)
8. 1.558 1.730 ↓ 1.0 4,974 1

Bitmap Heap Scan on chat_message cm (cost=94.59..524.37 rows=4,942 width=12) (actual time=0.205..1.730 rows=4,974 loops=1)

  • Recheck Cond: (sender_id = 1)
  • Heap Blocks: exact=368
9. 0.172 0.172 ↓ 1.0 4,974 1

Bitmap Index Scan on chat_message_sender_id_idx (cost=0.00..93.36 rows=4,942 width=0) (actual time=0.172..0.172 rows=4,974 loops=1)

  • Index Cond: (sender_id = 1)
10. 0.005 2.944 ↑ 1.0 1 1

Hash (cost=502.31..502.31 rows=1 width=12) (actual time=2.944..2.944 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.002 2.939 ↑ 1.0 1 1

Subquery Scan on cgr (cost=502.30..502.31 rows=1 width=12) (actual time=2.938..2.939 rows=1 loops=1)

12. 0.002 2.937 ↑ 1.0 1 1

Limit (cost=502.30..502.30 rows=1 width=12) (actual time=2.936..2.937 rows=1 loops=1)

13. 0.927 2.935 ↑ 5,030.0 1 1

Sort (cost=502.30..514.87 rows=5,030 width=12) (actual time=2.935..2.935 rows=1 loops=1)

  • Sort Key: chat_group_read.read_time DESC
  • Sort Method: top-N heapsort Memory: 25kB
14. 1.767 2.008 ↑ 1.0 4,963 1

Bitmap Heap Scan on chat_group_read (cost=95.27..477.15 rows=5,030 width=12) (actual time=0.281..2.008 rows=4,963 loops=1)

  • Recheck Cond: (account_id = 1)
  • Heap Blocks: exact=319
15. 0.241 0.241 ↑ 1.0 4,963 1

Bitmap Index Scan on chat_group_read_account_id_idx (cost=0.00..94.02 rows=5,030 width=0) (actual time=0.241..0.241 rows=4,963 loops=1)

  • Index Cond: (account_id = 1)
Planning time : 0.344 ms
Execution time : 11.498 ms