explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ooXW

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 282.009 ↓ 0.0 0 1

Hash Left Join (cost=270.73..24,376.32 rows=654 width=527) (actual time=282.009..282.009 rows=0 loops=1)

  • Hash Cond: (members3_.user_id = user4_.id)
  • Buffers: shared hit=36470
2. 0.000 282.007 ↓ 0.0 0 1

Hash Left Join (cost=238.74..24,335.35 rows=654 width=62) (actual time=282.007..282.007 rows=0 loops=1)

  • Hash Cond: (chat0_.id = members3_.chat_id)
  • Buffers: shared hit=36470
3. 0.001 282.007 ↓ 0.0 0 1

Nested Loop (cost=182.29..24,267.50 rows=299 width=31) (actual time=282.007..282.007 rows=0 loops=1)

  • Buffers: shared hit=36470
4. 0.782 282.006 ↓ 0.0 0 1

Subquery Scan on chatview1_ (cost=182.02..24,096.04 rows=299 width=4) (actual time=282.006..282.006 rows=0 loops=1)

  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 677
  • Buffers: shared hit=36470
5. 0.323 2.300 ↓ 1.1 677 1

Sort (cost=182.02..183.51 rows=598 width=24) (actual time=2.156..2.300 rows=677 loops=1)

  • Sort Key: u.last_updated DESC
  • Sort Method: quicksort Memory: 56kB
  • Buffers: shared hit=70
6. 0.176 1.977 ↓ 1.1 677 1

Hash Join (cost=133.88..154.44 rows=598 width=24) (actual time=1.747..1.977 rows=677 loops=1)

  • Hash Cond: (c.id = u.chat_id)
  • Buffers: shared hit=67
7. 0.084 0.084 ↓ 1.1 677 1

Seq Scan on chat c (cost=0.00..18.98 rows=598 width=4) (actual time=0.009..0.084 rows=677 loops=1)

  • Buffers: shared hit=13
8. 0.119 1.717 ↓ 1.1 677 1

Hash (cost=126.40..126.40 rows=598 width=12) (actual time=1.717..1.717 rows=677 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
  • Buffers: shared hit=54
9. 0.100 1.598 ↓ 1.1 677 1

Subquery Scan on u (cost=118.93..126.40 rows=598 width=12) (actual time=1.455..1.598 rows=677 loops=1)

  • Buffers: shared hit=54
10. 0.216 1.498 ↓ 1.1 677 1

Sort (cost=118.93..120.42 rows=598 width=12) (actual time=1.454..1.498 rows=677 loops=1)

  • Sort Key: (max(GREATEST(c_1.created_date, cm."timestamp"))) DESC
  • Sort Method: quicksort Memory: 56kB
  • Buffers: shared hit=54
11. 0.464 1.282 ↓ 1.1 677 1

HashAggregate (cost=85.37..91.35 rows=598 width=12) (actual time=1.185..1.282 rows=677 loops=1)

  • Group Key: c_1.id
  • Buffers: shared hit=54
12. 0.385 0.818 ↓ 1.4 1,412 1

Hash Right Join (cost=26.46..77.58 rows=1,038 width=20) (actual time=0.294..0.818 rows=1,412 loops=1)

  • Hash Cond: (cm.chat_id = c_1.id)
  • Buffers: shared hit=54
13. 0.172 0.172 ↓ 1.1 1,169 1

Seq Scan on chat_message cm (cost=0.00..48.38 rows=1,038 width=16) (actual time=0.006..0.172 rows=1,169 loops=1)

  • Buffers: shared hit=38
14. 0.123 0.261 ↓ 1.1 677 1

Hash (cost=18.98..18.98 rows=598 width=12) (actual time=0.261..0.261 rows=677 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
  • Buffers: shared hit=13
15. 0.138 0.138 ↓ 1.1 677 1

Seq Scan on chat c_1 (cost=0.00..18.98 rows=598 width=12) (actual time=0.005..0.138 rows=677 loops=1)

  • Buffers: shared hit=13
16.          

SubPlan (forSubquery Scan)

17. 51.520 278.924 ↑ 1.0 1 677

Nested Loop Left Join (cost=0.28..79.67 rows=1 width=8) (actual time=0.245..0.412 rows=1 loops=677)

  • Join Filter: (cm_1.id = ci.member_id)
  • Rows Removed by Join Filter: 1048
  • Buffers: shared hit=36400
18. 0.677 120.506 ↑ 1.0 1 677

Nested Loop (cost=0.28..47.66 rows=1 width=12) (actual time=0.095..0.178 rows=1 loops=677)

  • Buffers: shared hit=20318
19. 2.031 2.031 ↑ 1.0 1 677

Index Only Scan using chat_pkey on chat c_2 (cost=0.28..4.29 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=677)

  • Index Cond: (id = chatview1_.id)
  • Heap Fetches: 677
  • Buffers: shared hit=2039
20. 117.798 117.798 ↑ 1.0 1 677

Seq Scan on chat_member cm_1 (cost=0.00..43.36 rows=1 width=20) (actual time=0.091..0.174 rows=1 loops=677)

  • Filter: ((deleted IS FALSE) AND (active IS TRUE) AND (chat_id = chatview1_.id))
  • Rows Removed by Filter: 1470
  • Buffers: shared hit=18279
21. 106.898 106.898 ↓ 1.1 750 946

Seq Scan on chat_invite ci (cost=0.00..23.67 rows=667 width=8) (actual time=0.001..0.113 rows=750 loops=946)

  • Buffers: shared hit=16082
22. 0.000 0.000 ↓ 0.0 0

Index Scan using chat_pkey on chat chat0_ (cost=0.28..0.57 rows=1 width=31) (never executed)

  • Index Cond: (id = chatview1_.chat_id)
23. 0.000 0.000 ↓ 0.0 0

Hash (cost=40.09..40.09 rows=1,309 width=31) (never executed)

24. 0.000 0.000 ↓ 0.0 0

Seq Scan on chat_member members3_ (cost=0.00..40.09 rows=1,309 width=31) (never executed)

25. 0.000 0.000 ↓ 0.0 0

Hash (cost=28.43..28.43 rows=284 width=453) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=15.39..28.43 rows=284 width=453) (never executed)

  • Hash Cond: (userperson5_.user_id = user4_.id)
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on user_personalization userperson5_ (cost=0.00..12.40 rows=240 width=304) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.84..11.84 rows=284 width=149) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Seq Scan on "user" user4_ (cost=0.00..11.84 rows=284 width=149) (never executed)