explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5sis

Settings
# exclusive inclusive rows x rows loops node
1. 0.784 22.099 ↓ 1.3 1,129 1

Sort (cost=790.33..792.53 rows=881 width=78) (actual time=21.988..22.099 rows=1,129 loops=1)

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

Hash Join (cost=668.28..747.24 rows=881 width=78) (actual time=20.043..21.315 rows=1,129 loops=1)

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

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

  • Recheck Cond: (member_ids @> '{1}'::integer[])
  • Heap Blocks: exact=52
4. 0.104 0.104 ↓ 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.104..0.104 rows=1,798 loops=1)

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

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

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

HashAggregate (cost=562.17..586.88 rows=2,471 width=20) (actual time=18.355..19.145 rows=3,155 loops=1)

  • Group Key: cm.chat_group_id
7. 1.767 16.517 ↓ 2.0 4,974 1

Hash Left Join (cost=95.31..543.63 rows=2,471 width=12) (actual time=13.526..16.517 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.279 1.470 ↓ 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.235..1.470 rows=4,974 loops=1)

  • Recheck Cond: (sender_id = 1)
  • Heap Blocks: exact=368
9. 0.191 0.191 ↓ 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.191..0.191 rows=4,974 loops=1)

  • Index Cond: (sender_id = 1)
10. 0.004 13.280 ↑ 1.0 1 1

Hash (cost=0.71..0.71 rows=1 width=12) (actual time=13.280..13.280 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
11. 0.001 13.276 ↑ 1.0 1 1

Subquery Scan on cgr (cost=0.41..0.71 rows=1 width=12) (actual time=13.275..13.276 rows=1 loops=1)

12. 0.002 13.275 ↑ 1.0 1 1

Limit (cost=0.41..0.70 rows=1 width=12) (actual time=13.274..13.275 rows=1 loops=1)

13. 13.273 13.273 ↑ 5,030.0 1 1

Index Scan using chat_group_read_order_idx on chat_group_read (cost=0.41..1,438.85 rows=5,030 width=12) (actual time=13.273..13.273 rows=1 loops=1)

  • Index Cond: (account_id = 1)
Planning time : 0.653 ms
Execution time : 22.353 ms