explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oiCN

Settings
# exclusive inclusive rows x rows loops node
1. 0.096 32,131.756 ↓ 2.3 21 1

Sort (cost=503,615.08..503,615.10 rows=9 width=1,344) (actual time=32,131.746..32,131.756 rows=21 loops=1)

  • Sort Key: m.timecreated DESC
  • Sort Method: quicksort Memory: 44kB
2. 0.038 32,131.660 ↓ 2.3 21 1

Nested Loop Anti Join (cost=2.69..503,614.93 rows=9 width=1,344) (actual time=4,655.525..32,131.660 rows=21 loops=1)

  • Join Filter: (mua.messageid = m.id)
3. 0.054 32,131.580 ↓ 2.3 21 1

Nested Loop Left Join (cost=2.26..503,613.04 rows=9 width=1,344) (actual time=4,655.501..32,131.580 rows=21 loops=1)

  • Join Filter: (mub2.blockeduserid = u2.id)
4. 0.067 32,131.505 ↓ 2.3 21 1

Nested Loop Left Join (cost=1.98..503,610.40 rows=9 width=1,344) (actual time=4,655.494..32,131.505 rows=21 loops=1)

  • Join Filter: (mub.blockeduserid = u.id)
5. 0.073 32,131.396 ↓ 2.3 21 1

Nested Loop (cost=1.70..503,607.77 rows=9 width=1,344) (actual time=4,655.475..32,131.396 rows=21 loops=1)

6. 0.138 32,131.176 ↓ 2.3 21 1

Nested Loop (cost=1.28..503,603.49 rows=9 width=1,336) (actual time=4,655.464..32,131.176 rows=21 loops=1)

  • Join Filter: ((m.conversationid = mc.id) AND ((m.useridfrom <> mcm.userid) OR (mc.type = 2)))
  • Rows Removed by Join Filter: 3
7. 0.355 32,130.750 ↓ 2.7 24 1

Nested Loop (cost=0.85..503,599.17 rows=9 width=1,344) (actual time=4,655.434..32,130.750 rows=24 loops=1)

8. 273.787 32,130.395 ↓ 2.7 24 1

Nested Loop (cost=0.43..503,593.78 rows=9 width=1,336) (actual time=4,655.418..32,130.395 rows=24 loops=1)

  • Index Cond: (id = m.useridfrom)
  • Filter: (deleted = 0)
9. 31,366.999 31,366.999 ↑ 1.0 163,203 1

Seq Scan on mdl_messages m (cost=0.00..411,671.92 rows=163,797 width=1,320) (actual time=0.125..31,366.999 rows=163,203 loops=1)

  • Filter: (smallmessage ~~* '%test%'::text)
  • Rows Removed by Filter: 1993193
10. 489.609 489.609 ↓ 0.0 0 163,203

Index Scan using mdl_messconvmemb_con_ix on mdl_message_conversation_members mcm (cost=0.43..0.55 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=163,203)

  • Index Cond: (conversationid = m.conversationid)
  • Filter: ((m.useridfrom = 85394) OR (userid = 85394))
  • Rows Removed by Filter: 2l_user u (cost=0.42..0.59 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=24)e--
11. 0.288 0.288 ↑ 1.0 1 24

Index Scan using mdl_messconv_id_pk on mdl_message_conversations mc (cost=0.42..0.46 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=24)

  • Index Cond: (id = mcm.conversationid)
12. 0.147 0.147 ↑ 1.0 1 21

Index Scan using mdl_user_id_pk on mdl_user u2 (cost=0.42..0.47 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=21)

  • Index Cond: (id = mcm.userid)
  • Filter: (deleted = 0)
13. 0.027 0.042 ↓ 0.0 0 21

Materialize (cost=0.28..2.50 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=21)

14. 0.015 0.015 ↓ 0.0 0 1

Index Scan using mdl_messuserbloc_use_ix on mdl_message_users_blocked mub (cost=0.28..2.50 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (userid = 85394)
15. 0.019 0.021 ↓ 0.0 0 21

Materialize (cost=0.28..2.50 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=21)

16. 0.002 0.002 ↓ 0.0 0 1

Index Scan using mdl_messuserbloc_use_ix on mdl_message_users_blocked mub2 (cost=0.28..2.50 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (userid = 85394)
17. 0.021 0.042 ↓ 0.0 0 21

Materialize (cost=0.43..1.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=21)

18. 0.021 0.021 ↓ 0.0 0 1

Index Only Scan using mdl_messuseracti_usemesact_uix on mdl_message_user_actions mua (cost=0.43..1.76 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=1)

  • Index Cond: ((userid = 85394) AND (action = 2))
  • Heap Fetches: 0
Planning time : 16.869 ms
Execution time : 32,131.907 ms